Questions tagged [explain]
The EXPLAIN statement can be used to obtain information about the database executes a statement.
217
questions
0
votes
0
answers
27
views
Help me understand EXPLAIN PLAN of mysql 8
-> Sort row IDs: users.userid (actual time=24875.751..34999.822 rows=2547647 loops=1)
-> Table scan on <temporary> (cost=0.00..0.50 rows=6083517) (actual time=0.027..2488.740 rows=...
0
votes
0
answers
25
views
PostgreSQL query times out when selecting a smaller amount of data (different query plan)
PostgreSQL 15.2. We have a table like this (simplified):
create table behavior.event_59 (
event_id integer not null primary key,
second_offset integer not null,
-- ... more columns
)
...
- 188
0
votes
1
answer
53
views
Best way of SELECT on large table with filter to filter out around 1m rows with indexes
I have a table billing_billcycleorders that contains a foreign key billing_cycle_id. There are around 0.9m records for a particular billing_cycle_id. I want to select the data in the chunks for ~5000 (...
- 333
1
vote
1
answer
41
views
Different results from Postgres EXPLAIN ANALYZE
I was investigating the performance of a query that hit the top of my list in PMM and noticed strange behavior. The first time the requests execution time is long, subsequent times quickly:
Aggregate ...
0
votes
0
answers
31
views
PostgreSQL: Python UDF not shown in query plan obtained by EXPLAIN ANALYZE
I have a question regarding the execution of a Python UDF. Let's suppose I have a UDF named testUDF(...) and I apply it to table "testtable". When I run the query SELECT testUDF(...) from ...
- 3
0
votes
2
answers
82
views
How to identify sub query in Mariadb Explain
I'm trying to analyze the performance of a complex query in MariaDB (about 15 sub-queries).
For that I use the Explain command. The result comes with a lot of derived table (<derived2>, <...
- 131
0
votes
1
answer
87
views
ANALYZE or EXPLAIN for previous query
EXPLAIN SELECT ... shows the EXPLAIN for a query (without actually running it).
SHOW EXPLAIN FOR conn_id shows the EXPLAIN for a query that is currently running.
Is it possible to get the EXPLAIN for ...
- 748
0
votes
2
answers
71
views
Terrible performance on WP-generated query migrating from MySQL to MariaDB
I'm trying to migrate a website from a WP-focused hosting provider that uses Percona for their DB node to a Jelastic-based provider that offers MariaDB for its DB nodes.
There's one query in ...
- 21
1
vote
2
answers
99
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
21
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
89
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 ))
...
- 249
0
votes
0
answers
284
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
1
vote
1
answer
1k
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,...
- 155
0
votes
1
answer
306
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 ...
- 157
0
votes
2
answers
129
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
50
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,417
0
votes
1
answer
104
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
2
votes
2
answers
977
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
84
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
...
- 55
1
vote
1
answer
46
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
251
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
...
- 123
1
vote
0
answers
102
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 ...
- 189
1
vote
2
answers
1k
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 &...
- 113
0
votes
1
answer
349
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
1k
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 ...
- 225
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 ...
- 733
0
votes
2
answers
79
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
835
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
255
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 ...
- 389
2
votes
2
answers
1k
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 ...
- 137
2
votes
1
answer
157
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
139
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
57
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
217
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
322
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_....
- 163
0
votes
1
answer
35
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
121
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
48
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`.`...
- 201
1
vote
2
answers
2k
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 ...
- 23
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
...
- 19
3
votes
1
answer
498
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
665
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
31
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:
...
- 161
3
votes
1
answer
352
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
137
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
-1
votes
0
answers
56
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 (...
- 55
0
votes
1
answer
30
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
114
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 ...
- 197
1
vote
0
answers
39
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 ...
- 121
-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 ...
- 99