Questions tagged [explain]

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

Filter by
Sorted by
Tagged with
1
vote
2answers
26 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
29 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 ...
1
vote
2answers
70 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
44 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 ...
4
votes
1answer
74 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
23 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 ...
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`.`...
0
votes
1answer
93 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
2answers
33 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
33 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) ...
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 ...
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
54 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
32 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 (...
0
votes
0answers
49 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 ...
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 ...
-1
votes
1answer
34 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
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
109 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, ...
0
votes
1answer
41 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
51 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 ...
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, (...
2
votes
1answer
171 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
101 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
23 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
259 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
49 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 ...
0
votes
1answer
127 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
0answers
284 views

MongoDB Explain plan - elapsed time breakdown for executionStats

I have run an explain plan for a query, and am a bit confused with the timings of each stage, and how they aggregate to the total elapsed time. Following command was run to generate the statistics, ...
0
votes
1answer
29 views

How to correctly interpret index usages from EXPLAIN of mysql?

I've exported the results of an EXPLAIN run on a query. What I find confusing is that there's the key column listing out one of the indexes from the list of possible_keys(not shown in picture) however ...
0
votes
0answers
83 views

How to trigger Mysql parallel query function to start?

Please help,how to trigger Mysql parallel query function to start? MySQL version is 8.0.19. mysql> show variables like 'innodb_parallel_read_threads'; +------------------------------+-------+ | ...
0
votes
1answer
32 views

How to run analysis on a 16M row table faster

I have two tables students with 6k records and transactions with 16M records. I have a query similar to this: select section, count(s.students) from students as s where upper(name) in (select ...
1
vote
1answer
788 views

Postgres EXPLAIN ANALYZE is super slow

there is Postgres 9.6 on Redhat I want to EXPLAIN ANALYZE slow query (quite complex one) and it takes too much time. My session with EXPLAIN ANALYZE consume 100% of one CPU and doesn't finish even ...
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 ...
1
vote
1answer
158 views

How to profile the PostgreSQL query planner diagnose what is causing it to take up large amounts of memory and time

I'm fighting with a specific "generated" query (the SQL is generated from a proprietary system, and I won't be able to share it or the execution plan here), and running into simply massive memory ...
0
votes
1answer
362 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. ...
2
votes
1answer
234 views

Time Difference Between actual total time and execution time in EXPLAIN ANALYZE

I have the following output of EXPLAIN ANALYZE query on PostgreSQL 11.5 EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT actor.actor_id, actor.first_name, act.num FROM actor INNER JOIN ( SELECT ...
1
vote
1answer
175 views

MySQL uses range and where on UPDATE with the primary key

I have these simple SELECT and UPDATE requests on MySQL using the primary key, there are about 13 million rows in the table. When running an EXPLAIN on both, I notice that the UPDATE one does not use ...
0
votes
0answers
27 views

How to index on joined tables

Total table_parent rows: 1318 Total table_child rows: 12 mil++ EXPLAIN select SQL_CALC_FOUND_ROWS table_child.* , table_parent.name AS parent_name from `table_child` inner join `table_parent` ...
1
vote
1answer
32 views

Why does performing a query on a second environment, it takes forever to run, and scans hundreds of millions of rows?

I have a query, it is fairly big, but not THAT big. One the baseline environment, it runs in less than 1/3 of a second. When we moved the database to RDS in Amazon, it takes 10-15 minutes to run. I ...
0
votes
1answer
35 views

How to optimize query based on explain analyze in PostgreSQL?

I am running the following query via EXPLAIN ANALYZE but it is taking a bit of a long time to run. Based on the output of EXPLAIN ANALYZE, what are some things I can do to my table to make this query ...
0
votes
2answers
493 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, ...