Questions tagged [explain]

The EXPLAIN statement can be used to obtain information about the database executes a statement.

Filter by
Sorted by
Tagged with
0
votes
2answers
527 views

PostgreSQL 10 Optimizing slow query performance

I have the following query: select i.id as id, i.ts_updated as updated, i."name" as name, cast(attr.data_norm as text) as data_norm, cast(_59.json_agg as text) as _59, ...
0
votes
1answer
133 views

MYSQL Select query stuck at “Sending data”

I have an select query with about 1M records, I'm working on Magento 1.9 database. SELECT IF(sup_ap.is_percent = 1, TRUNCATE(mt.value + (mt.value * sup_ap.pricing_value / 100), 4), mt....
0
votes
1answer
40 views

Postgres SELECT query produces exact same explain analyze results but different execution times in the results

I have a SELECT query which when running produces the exact same plan in terms of indexes being used when doing an explain analyze however the duration of the run is different (2 seconds vs 30 seconds ...
1
vote
2answers
82 views

What did I miss in EXPLAIN that hinted at server crippling loads?

I reviewed EXPLAIN ANALYZE output for a query and found a couple subqueries were scanning the whole table. This is done to get only the most recent record in those tables related to an appointment. ...
0
votes
2answers
50 views

Complex `where` dramatically affects query performance

There are much more complex queries running on these tables that are much faster but this one takes 90+ seconds to run. What am I doing wrong here? How can this be improved? SELECT r.id ...
1
vote
2answers
33 views

Get a real query plan for EXPLAIN DELETE

I have a delete query that runs a long time. I try to explain it: EXPLAIN DELETE FROM matches WHERE id = 1 And I get: QUERY PLAN Delete on matches (cost=0.43..2.65 rows=1 width=6) -> Index ...
2
votes
1answer
32 views

Postgres condition pushdown when using range conditions (>=, <= or BETWEEN)

I want to understand better the circumstances under which Postgres query executioner pushes conditions down into the join components. I would be especially grateful for references to source code parts ...
5
votes
2answers
7k views

Postgres multiple joins slow query, how to store default child record

Ive got a small database in Postgres, about 10,000 records storing company customers. I have a "slow" performing query (about half a second) which is performed very frequently, and my boss wants me to ...
4
votes
1answer
77 views

Non-deterministic performance of query on select, from 1s to 60s on table with 1 billion rows

I'm trying to investigate why the performance of this query is so non-deterministic. It can take anywhere from 1 seconds, to 60 seconds and above. The nature of the query is to select a "time ...
0
votes
2answers
28 views

Weird tables with numbers and underscores in PostgreSQL execution plans

I'm debugging performance problems with my DB and see some unusual entries in the DB log: 2021-04-21 01:07:33.857 UTC [14823] user@db LOG: duration: 933645.469 ms plan: Query Text: select systemv0_....
0
votes
1answer
31 views

how can improve this query in big mysql database

my query is simple but 10 seconds long to respond. please check and help me to improve that SELECT COUNT(test.code) AS sumid FROM test WHERE test.state = '03' AND test.type = '0' ...
0
votes
1answer
25 views

Anyway to view original query on top of views using postgres

For example, I have a simple query: SELECT * FROM information_schema.columns where table_schema = 'public'; Which turn out very complex, information_schema.columns is a complex view. Right now, to ...
1
vote
3answers
2k views

MySQL DELETE statement doesn't use index although the same SELECT query does

I've got a table with ~30 million rows (and soon twice/triple times more) where I have to do quite regular updates. The table structure is like the following: id, cookie_id VARCHAR(45), country ...
0
votes
1answer
39 views

Slow SQL taking almost 1s to execute with only 24000 records! (EXPLAIN included)

Any ideas how to improve and what the actual culprit is? records in product are only 24000 takes 0.85s to run Mysql 5.7 select `product`.* from `product` inner join `manufacturer` on `product`.`...
2
votes
2answers
4k views

How to get the size of the result of a SQL query?

How do you get the size (in bytes) of the result of a SQL query, in MySQL 5.6? EXPLAIN will return the number of fetched rows, but not their size. My aim is to evaluate common queries to know a ...
0
votes
1answer
105 views

Postgres chooses seq scan over index scan

I have the following query: SELECT * FROM foo LEFT OUTER JOIN foo_history ON foo.id = foo_history.foo_id WHERE foo.entity_id = 11111 foo table is around 8Million rows and foo_history is around ...
1
vote
1answer
179 views

A MySQL EXPLAIN number of rows discrepancy

MySQL 5.5.49-log More questions on the query in Why does it use temporary? (MySQL) (the query is the same but the question is different): I have the following table (filled with many rows): CREATE ...
1
vote
2answers
38 views

Under what circumstances would these 3 queries return different number of results?

Hello I have the following question: Query 1: SELECT b.id,     (     SELECT SUM(cb.invoice_amount) AS sum     FROM payment_lines cb     WHERE cb.batch_id = b.id     ) AS batch_total_invoice_amount ...
3
votes
1answer
36 views

Does a huge key length value for a mulibyte column affect the index performance?

When I look at the EXPLAIN results, the key len value is always calculated based on the actual column length multiplied on the maximum number of bytes for the chosen encoding. Say, for a varchar(64) ...
0
votes
1answer
400 views

Performance issues with CAST function

Observing this performance issue that can't be explained with EXPLAIN in a PostgreSQL 9.6 database. There is currently a table public.users, which has a cast on it to output a record to JSONb. (e.g. ...
5
votes
2answers
526 views

Postgres Row Level Security policy optimizes poorly compared to inline version

I have a query which looks like this: SELECT post.id, post.author_id, post.published_at, post.content FROM post WHERE post.group_id = 1 ORDER BY post.published_at DESC, post.id LIMIT 5; ...
1
vote
0answers
50 views

SQL: ON vs. WHERE in SEMI JOIN

Considering the following two (equivalent) SQL statements. Why do their execution plan differ (especially in performance) that much? I'm using Postgres 11.9 in this example. select p.name from person ...
1
vote
0answers
17 views

Analyzing performance of an insert int postgres

I have a statement which is inserting a bunch of rows (doesn't really matter what or where for this question) into a Postgres DB and it isn't as fast as I'd like. I can run an explain query to see ...
25
votes
1answer
19k views

EXPLAIN ANALYZE shows no details for queries inside a plpgsql function

I am using a PL/pgSQL function in PostgreSQL 9.3 with several complex queries inside: create function f1() returns integer as $$ declare event tablename%ROWTYPE; .... .... begin FOR event IN ...
2
votes
2answers
83 views

Given the following tables/index/explain planning, why is the query so slow?

I have the following tables: CREATE TABLE trace ( uuid uuid NOT NULL, result text, ruleid uuid, previousruleresultid uuid, senderduns text, receiverduns text, eventid uuid, ...
0
votes
0answers
20 views

UPDATE vs SELECT returns different Execution Plan

In my DB table, where ID is the PK, if I use this: EXPLAIN SELECT * FROM table WHERE ID = 3; The type and the ref columns of the explain output table are both equal to const. Now, when I use this: ...
3
votes
1answer
70 views

Interpreting startup time and varying plans for Postgres seq scans

In asking a recent question, some mysterious startup time components came up in my EXPLAIN ANALYZE output. I was playing further, and discovered that the startup time drops to near 0 if I remove the ...
1
vote
1answer
36 views

how to sum times in EXPLAIN for Postgres parallel query

I'm having trouble understanding this EXPLAIN on Postgres 12.3: EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT count(1) FROM mytable WHERE page ~ 'foo'; This is a 22GB table with 30 million rows, on a ...
0
votes
0answers
33 views

Postgresql - extremely slow basic SELECT query

I have a materialized view with about 1.6 million entries that gets refreshed every hour or so. I have a && and ANY() as the filters in the query. I observed the query was taking too long (...
125
votes
4answers
123k views

Optimizing queries on a range of timestamps (two columns)

I use PostgreSQL 9.1 on Ubuntu 12.04. I need to select records inside a range of time: my table time_limits has two timestamp fields and one integer property. There are additional columns in my ...
0
votes
0answers
63 views

Understanding rows_examined_per_scan and rows_produced_per_join

I'm not mysql guy, but I've got opportunity to work with that recently - I've got to optimize some query on Mysql5.7 (I have to cover that also on 5.6, but started with 5.7 as it has apparently more ...
0
votes
1answer
26 views

Read explain results from oracle database

hi currently i have query like this on oracle database 12 Query Detail because script is too long if input here the query is too long execute time from explain results i got this PLAN_TABLE_OUTPUT ...
1
vote
1answer
36 views

See content of temporary Table in Oracles' EXPLAIN

I'm executing a Query in Oracle SQL Developer and eventually call SELECT * FROM TABLE ( dbms_xplan.display ); to get an impression what Oracle just did. There's a column Name in the output that ...
2
votes
1answer
2k views

What's the difference between DISTINCT_SCAN and IXSCAN in explain output?

In Mongo 3.4 explain("executionStats").distinct(...), for some queries the winning plan contains an IXSCAN step, and for other queries it contains an DISTINCT_SCAN step. What is the difference ...
-1
votes
1answer
35 views

How is the value of row column is estimated in EXPLAIN statement in mysql?

I have the following setup: create table Test(id integer, itemID integer, title varchar(100) ,PRIMARY KEY (id, itemID)); insert into Test(id, itemID, title) values(1,678, "Hello"); insert ...
1
vote
0answers
27 views

Extreme slow query for a simple indexed selection - Why? [duplicate]

i have a table in my database that is described as follows: -- Table: public.devicemeasurement_mqtt -- DROP TABLE public.devicemeasurement_mqtt; CREATE TABLE public.devicemeasurement_mqtt ( ...
0
votes
1answer
19 views

Why is MySQL using this index? Can I force it to use another?

I have a query, and when I call EXPLAIN for it, it appears that my db is using an unhelpful index and ignoring a useful index. EXPLAIN SELECT id, full_name, agent_id, brand_id FROM `mytable` WHERE ( ...
0
votes
0answers
24 views

Select Type UNION RESULT doesn't appear in the output of Explain statement

EXPLAIN SELECT actor_id, (SELECT 1 FROM sakila.film_actor WHERE film_actor.actor_id = der_1.actor_id LIMIT 1) FROM ( SELECT actor_id FROM sakila.actor LIMIT 5 ) AS der_1 UNION ALL SELECT film_id, (...
0
votes
4answers
139 views

MySQL optimizer won't use my index

With the following query: SELECT * FROM geoname LEFT JOIN alternatename ON geoname.geonameid = alternatename.geonameid AND alternatename.isPreferredName = 1 AND alternatename....
0
votes
1answer
33 views

PostgreSQL is skipping indexes when using `order by` + `limit`

I have a postgreSQL database with two big tables, connections and sessions select "c"."id" "id", "ch"."title" "channel", "ch".&...
1
vote
0answers
131 views

What is Oracle's Window Buffer Operator?

Oracle database's explain plan sometimes includes Window (Buffer). What does this operator do? Here is the SQL. The primary keys are RECORD_DATE, TYPE and KEY. select source.RECORD_DATE, ...
20
votes
2answers
23k views

Why is count(*) slow, when explain knows the answer?

This query: select count(*) from planner_event takes a very long time to run - so long, I gave up and killed it before it finished. However, when I run explain select count(*) from planner_event, I ...
0
votes
1answer
48 views

MySQL read explain - using where

In Oracle there is an important note in the explain plain where it says filter a.col1=... this one letting you know col1 is first retrieved from the disk and only then filter is been done. This ...
1
vote
1answer
28 views

Optimising query: not sure why LOWER() index isn't used

I've got comments that have 1..N comment_notifications. Each notification basically contains a username of the user notified. Each notification can be resolved_at when the user is done with it. This ...
0
votes
2answers
66 views

How can I see execution time of an ALTER TABLE statement?

Is it possible to see the execution time of an alter table command? I think with \timing on in psql, it shows more than the execution time, but also the client round trip, and maybe other things as ...
2
votes
1answer
188 views

How to optimize Merge Anti Join?

While trying to find the fastest way to do a bulk update of a large table, I came up with the following plan: having main_table (potentially above 1000 mln rows) and update table up to a few million ...
0
votes
2answers
124 views

Postgres 10.6: Why is a particular partition's index not being used?

I'm truly stumped as to what's causing this issue and hoping for some guidance. Firstly, this is in our STAGE environment and we partition this table based on the createdTime every month. I have ...
0
votes
1answer
25 views

How to get the result of MySQL explain query into a file?

I am unable to get the results of the explain query in its normal tabular format or json format into a file. select * from table_name into outfile 'file.format'; select trace from information_schema....
2
votes
1answer
295 views

Very slow postgresql aggregation

EDIT: docker image for testing with the same data (obfuscated) $ docker run --rm --name pg -d homer5439/pgagg $ docker exec -ti pg bash # createdb -U postgres test; zcat /tmp/corr.sql.gz | psql -U ...
0
votes
1answer
50 views

Explain shows index doesnt working but i have an index

The query in MySQL is: EXPLAIN EXTENDED SELECT id, username, name, last_name, IF(friend IS NOT NULL,1,0) as friend, activated, if(private=1 AND IF(friend IS ...