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

How can I make queries on skewed datasets more performant on Postgres

I have a seemingly simple query that's unfortunately extremely slow. I believe that I know why it is slow, but not how to make it fast, so I'd like to see how this could be improved. let's call the ...
0 votes
2 answers
19 views

Oracle query different performances between prod and staging environment

So I have those 2 Oracle 12c databases R2, prod and staging environment, both are perfectly aligned and running on identical hardware. The same select statement will take about 400ms on staging while ...
  • 101
0 votes
1 answer
35 views

How are HashAggregate row estimates calculated?

I have the following query: SELECT foo.* FROM foo WHERE foo."id" IN ((SELECT bar."id" FROM bar WHERE bar.user_id IN ( 100, 102 )) ...
  • 207
0 votes
0 answers
40 views

Postgres - Debugging slow query

we are using postgres 13.7 My company runs a fairly complicated analytical query, unfortunately, I'm not allowed to disclose the query here. The explain plan has around 30 nodes and joins about 10 ...
  • 135
0 votes
1 answer
280 views

MySQL does not use index, does full table scan instead

EDIT: See further developments below. I have an InnoDB table with the following schema: CREATE TABLE `data` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `app_id` CHAR(27) NULL DEFAULT NULL,...
0 votes
1 answer
74 views

Oracle How to get Execution Plan for SQL executed inside of PLSQL?

Is it possible to examine the execution plan for a SQL statement executed inside a PLSQL block? DECLARE l_count PLS_INTEGER; BEGIN SELECT COUNT(1) INTO l_count FROM foo; END; / For ...
0 votes
2 answers
47 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 ...
  • 141
1 vote
1 answer
35 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 ...
  • 1,387
0 votes
1 answer
42 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 ...
  • 1,690
1 vote
2 answers
443 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 ...
0 votes
1 answer
82 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 ...
  • 57
1 vote
1 answer
42 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 ...
0 votes
0 answers
104 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 ...
1 vote
0 answers
67 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 ...
  • 179
1 vote
2 answers
560 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 &...
0 votes
1 answer
196 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 ...
  • 162
0 votes
2 answers
736 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 ...
0 votes
0 answers
20 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 ...
  • 725
0 votes
2 answers
73 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, ...
0 votes
2 answers
349 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. ...
  • 129
0 votes
1 answer
196 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 ...
  • 195
1 vote
2 answers
366 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
1 answer
98 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 ...
  • 121
1 vote
2 answers
126 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. ...
  • 133
0 votes
2 answers
56 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 ...
5 votes
1 answer
160 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 ...
  • 53
0 votes
2 answers
144 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_....
  • 153
0 votes
1 answer
34 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' ...
  • 3
0 votes
1 answer
70 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 ...
  • 101
0 votes
1 answer
46 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`.`...
  • 191
0 votes
1 answer
793 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 ...
  • 13
1 vote
2 answers
49 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
1 answer
280 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
0 answers
359 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 ...
  • 111
0 votes
0 answers
27 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: ...
  • 149
3 votes
1 answer
251 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 ...
  • 189
1 vote
1 answer
120 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 ...
  • 189
0 votes
0 answers
50 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 (...
  • 57
0 votes
1 answer
28 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 ...
  • 47
1 vote
1 answer
81 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
0 answers
32 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
1 answer
37 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
0 answers
30 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 ( ...
  • 181
0 votes
1 answer
24 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
1 answer
211 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
0 answers
315 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, ...
  • 111
0 votes
1 answer
213 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 ...
  • 437
1 vote
1 answer
31 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 ...
  • 187
0 votes
2 answers
519 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
0 answers
29 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, (...
  • 123

1
2 3 4 5