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
2 answers
22 views

logging EXPLAIN output of slow queries in MySQL

I am trying to understand if there is functionality like auto_explain in PostgreSQL for MySQL. Specifically, I am interested in logging the query plan (not just the query) for all slow queries in ...
user avatar
  • 101
1 vote
1 answer
27 views

Get explain plan for query with inline function

I have a query that has an inline function: with function with_f(p_text in varchar2) return varchar2 is begin return p_text; end; select with_f(dummy) from dual Is there a way to get the ...
user avatar
  • 1,373
0 votes
1 answer
26 views

Can EXPLAIN be used to get some insights about Common Table Expression (CTE)?

I need to find the ancestors in a table having an ID value and a Google research point me to recursive Common Table Expression (CTE) I wonder if EXPLAIN can be used to get some insights on how MySQL ...
user avatar
  • 1,690
1 vote
2 answers
178 views

Postgres slow exists subquery

I have a queue system that has two tables, jobs and jobs_queues. jobs is where we put jobs to run and job_queues we populate with queue names so that when we pick jobs we can discard those that are ...
user avatar
0 votes
1 answer
76 views

Query optimization: LEFT JOIN makes the query incredibly slow

I have the following query with explain analyze and it's incredibly slow because of the LEFT JOIN. EXPLAIN ANALYZE SELECT DISTINCT ON (project_name, release_version, project_id, release_id) vbc.id ...
user avatar
  • 57
1 vote
1 answer
37 views

Specific index issue with postgresql planner/optimizer

i have an issue where i have no idea anymore and can't seem to find a similar one already discussed, so... Am/have to be on pgsql 10.15 / RHEL x86_64. rel A has about 813 million records. format is ...
user avatar
0 votes
0 answers
40 views

Different EXPLAIN ANALYZE results for the same query on different servers

I have a table named review with about 33 million rows. I have this query: EXPLAIN ANALYZE (WITH clicker_reviews AS ( SELECT monologue_id FROM review WHERE type = 'CORE' AND ...
user avatar
1 vote
0 answers
32 views

How to speed up GroupAggregate

https://explain.depesz.com/s/I2IN I have a query against a TimescaleDB 1.7 hypertable on Postgresql 12. Lots of joins to other table. When I do the grouping it seems to take a lot of time, I switched ...
user avatar
  • 179
1 vote
2 answers
218 views

Postgresql explain alter table add column doesn't work

I'm using Postgresql 12 (AWS RDS) and pgAdmin4. While the following statement works explain select * from table "Commands"; The next one explain alter table "Commands" drop column &...
user avatar
0 votes
1 answer
103 views

Postgres changes query plan for prepared statement to something poor after multiple invocations

Postgres 13.4 I have a prepared statement that is similar to this: PREPARE my_statement AS SELECT * FROM products WHERE (normalized_name = $1 OR distinct_normalized_names @> array['$1']) AND ...
user avatar
  • 162
0 votes
2 answers
423 views

Why does GROUP BY on table A cause "using temporary; using filesort" on table B? [closed]

Given the following contrived example code: SELECT A.author_name , COUNT(T.title) AS num_titles FROM authors A JOIN titles T ON T.authorFK = A.authorPK GROUP BY ...
user avatar
0 votes
0 answers
19 views

Force PostgreSQL not to think beyond what EXPLAIN says [duplicate]

EXPLAIN gives a good strategy but nevertheless the query (and EXPLAIN ANLALYZE) runs extremely slowly. Is there a simple way to force PostgreSQL always query by the same plan that EXPLAIN produces for ...
user avatar
  • 725
0 votes
2 answers
61 views

Seemingly simple query with unexpected execution time

I have the following schema : And want to get the last price (based on transaction_datetime) per test_acts.code using the following query : SELECT code, price FROM test_transaction_acts, ...
user avatar
0 votes
2 answers
129 views

Postgres: Faster query when adding useless JOIN

I was looking at a query to make it more performant and I encountered an interesting case. This query executes much faster when I add a (redundant) JOIN that doesn't change the actual result set. ...
user avatar
  • 129
0 votes
1 answer
125 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 ...
user avatar
  • 143
1 vote
2 answers
128 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 ...
user avatar
2 votes
1 answer
65 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 ...
user avatar
  • 121
1 vote
2 answers
110 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. ...
user avatar
  • 133
0 votes
2 answers
53 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 ...
user avatar
5 votes
1 answer
135 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 ...
user avatar
  • 53
0 votes
2 answers
66 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_....
user avatar
  • 153
0 votes
1 answer
32 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' ...
user avatar
  • 3
0 votes
1 answer
44 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 ...
user avatar
  • 101
0 votes
1 answer
42 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`.`...
user avatar
  • 191
0 votes
1 answer
494 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 ...
user avatar
  • 13
1 vote
2 answers
47 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 ...
user avatar
3 votes
1 answer
163 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) ...
user avatar
1 vote
0 answers
150 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 ...
user avatar
  • 111
0 votes
0 answers
22 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: ...
user avatar
  • 137
3 votes
1 answer
191 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 ...
user avatar
  • 189
1 vote
1 answer
81 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 ...
user avatar
  • 189
0 votes
0 answers
45 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 (...
user avatar
  • 57
0 votes
1 answer
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 ...
user avatar
  • 47
1 vote
1 answer
67 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 ...
user avatar
1 vote
0 answers
24 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 ...
user avatar
-1 votes
1 answer
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 ...
user avatar
1 vote
0 answers
28 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 ( ...
user avatar
  • 171
0 votes
1 answer
22 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 ( ...
user avatar
0 votes
1 answer
102 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".&...
user avatar
1 vote
0 answers
251 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, ...
user avatar
  • 111
0 votes
1 answer
171 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 ...
user avatar
  • 373
1 vote
1 answer
30 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 ...
user avatar
  • 187
0 votes
2 answers
302 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 ...
user avatar
0 votes
0 answers
27 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, (...
user avatar
  • 123
2 votes
1 answer
463 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 ...
user avatar
0 votes
2 answers
324 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 ...
user avatar
  • 321
0 votes
1 answer
50 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....
user avatar
  • 165
2 votes
1 answer
575 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 ...
user avatar
0 votes
1 answer
52 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 ...
user avatar
  • 3
0 votes
1 answer
226 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....
user avatar

1
2 3 4 5