Questions tagged [explain]
The EXPLAIN statement can be used to obtain information about the database executes a statement.
238
questions
1
vote
1
answer
68
views
Fetch wikipedia articles query optimization in mysql wikipedia Database
I have imported the Wikipedia database to a local MySQL database and I wrote a query to fetch wiki pages.
My query is as follows :
SELECT
p.page_title,
p.page_id,
t.id,
text.old_text
FROM ...
- 35
1
vote
1
answer
6k
views
How to optimize MySQL SUM query even after adding index
The below query is taking 11 min to return the result set even after adding indexes and changing the table row format to Compressed. The 2 tables in the below query each have Table 1:...
- 21
1
vote
1
answer
827
views
mysql query choosing an incorrect execution plan
I have a primary with an dependant subquery as a where condition that is choosing a wrong execution plan, and therefore is running for ever.
Main query:
SELECT rtr.id AS id156_,
rtr....
- 113
1
vote
2
answers
285
views
Can I get some rationale from the Optimzer why it chose a certain explain plan?
This is not about reading an explain plan.
This is not about adding any hints or measuring performance of a specific query.
I have always been wondering whether it's possible for a given explain ...
- 2,420
1
vote
2
answers
2k
views
Is there any manual for the visual explain in mysql? The output plan is unreadable
Just like this example, I cannot even tell the join ordering from it. And what does "ALL" and "ref" in the table mean? Is there any manual about the visual explain?
- 907
1
vote
1
answer
988
views
Why does it use temporary? (MySQL)
MySQL 5.
I have the following table (filled with many rows):
CREATE TABLE `SectorGraphs2` (
`Kind` tinyint(3) UNSIGNED NOT NULL COMMENT '1 - продюсер, 2 - жанр, 3 - регион',
`Criterion` tinyint(...
- 745
1
vote
1
answer
5k
views
Slow postgresql query with pagination and filtering
For the DB in question, I have followed the advice of Pagination Done the
PostgreSQL Way
That is, results are being paginated on a composite index with a definite sort order.
Now, my problem is ...
- 233
1
vote
1
answer
1k
views
postgres explain plan with giant gaps between operations
I have a postgresql explain plan that seems to have large gaps between the join, sort & filter processes and can't explain (sorry) it. The query is taking an inordinate amount of time and the ...
- 13
1
vote
1
answer
140
views
Does limit impact affected rows or not?
I have this table:
CREATE TABLE IF NOT EXISTS `usergroups` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(11) unsigned NOT NULL,
`group_id` smallint(5) unsigned NOT ...
- 663
1
vote
1
answer
92
views
Query optimization when no row is returned
I am using explain to figure out what is happening in my query which is:
explain select t from c where u1_id = 1 group by t;
I see "Using where" in the Extra column of the resultset. But this only ...
- 111
1
vote
2
answers
61
views
Is there a "correct" way for an optimizer to process a subquery if the outer query has additional where filters?
This topic came up for me as the result of investigating an error in Redshift. We have a table containing a column of numbers represented as text, but for rows where no value is present a single ...
- 111
1
vote
1
answer
87
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,457
1
vote
1
answer
49
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 ...
1
vote
1
answer
201
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
vote
1
answer
35
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 ...
- 197
1
vote
1
answer
134
views
Postgres logging_collector probably blocks auto_explain output
Postgres 10.4
Postgres 9.6
Problem on both versions
I want to collect postgresql slow queries plan, so I added to my postgresql.conf setting from documentation
session_preload_libraries = '...
- 123
1
vote
1
answer
3k
views
Explain plan gives different results depending on schema
I have a query that joins a multi million row table to one containing hundreds of thousands of rows.
Schema owner 'ABC' owns the smaller table and schema owner 'DEF' owns the larger table. They both ...
- 1,510
1
vote
1
answer
315
views
How to interpret this XML plan fragment?
I am looking at the query plan produced by SQL Server 2008 R2 and I'm not sure I'm interpreting it properly. Examples:
<RelOp NodeId="2" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="1" ...
- 26k
1
vote
1
answer
3k
views
mariadb - EXPLAIN works but ANALYZE throws ERROR 1064 (42000) error in SQL syntax
I want to see the execution plan of a query to find out why it is slow, but ANALYZE does not seem to work, it throws an error on the same query that EXPLAIN accepts.
MariaDB [dbx]> explain select `...
- 161
1
vote
1
answer
221
views
Help interpreting a MySQL execution plan
I'm trying to understand a slow query. The SELECT has been running (at this moment) for almost 18000 seconds (output from a SELECT on PROCESSLIST):
id: 22
time: 17870
...
- 337
1
vote
0
answers
39
views
PostgreSQL - Star Schema - Execute plan seems "dumb" and i can't figure out why
I'm using a postgresql (15.5) database with the following logic:
We have "meters" who measure electrical consumption.
We have "computer meters" who are calculation of the ...
1
vote
0
answers
29
views
Does aggregation order affect performance
I read on a blog that changing the order of aggregation (using group by 1,2 instead of group by 2,1 can affect the performance of the query.
But the costs of both the query plans are identical. Yet, ...
- 803
1
vote
1
answer
73
views
PostgreSQL recursive query performance improvement
I'm building a database which is used to derive hierarchical relations for which I chose a PostgreSQL recursive CTE .
my table structure is
table_name |column_name |data_type ...
- 13
1
vote
2
answers
286
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 ...
1
vote
1
answer
3k
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
1
vote
0
answers
133
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
50
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
1
vote
0
answers
1k
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
1
vote
1
answer
198
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 ...
- 217
1
vote
0
answers
41
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 ...
- 133
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
1
vote
0
answers
452
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,
...
- 113
1
vote
0
answers
1k
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, ...
- 11
1
vote
1
answer
44
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 ...
- 125
1
vote
0
answers
56
views
Different operations in different databases
So I have just executed EXPLAIN PLAN FOR on the same query in two databases that I've been told are identical in every regard except for slight variations in data amount.
And the result of it seems ...
- 121
1
vote
0
answers
63
views
Optimization Technique For Update Query in MySQL 5.1.73-log
I am trying to optimize an update query on InnoDB table in MySQL 5.1.73-log.
As per my understanding we can not use EXPLAIN on update query for 5.1 version.
So what will be the alternate course of ...
- 371
1
vote
1
answer
842
views
postgres auto_explain : setting log_min_duration doesn't start
postgres 8.4.20
With these settings, the daemon starts :
postgresql.conf
shared_preload_libraries = 'auto_explain'
With these settings, the daemon doesn't start :
postgresql.conf
...
- 121
1
vote
0
answers
600
views
MySQL deadlocking because update using full table scan
I have these jobs that run in parallel that call a series of queries on my DB. They're batched so each job is associated with a group of "bar" objects. So in the example below, temp1 is just a ...
- 111
1
vote
0
answers
1k
views
Postgresql Explain Analyze - help: actual time in loops
I'm trying to interpret an Explain Analyze, but I did not understand this:
According to the Postgresql documentation at: https://www.postgresql.org/docs/9.6/static/using-explain.html
" the loops ...
- 201
1
vote
1
answer
228
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 ...
- 745
1
vote
2
answers
1k
views
Should I stop an awfully inefficient MySQL DELETE statement?
I'm running the following statement on MySQL 5.5 in a 1GB 1CPU SSD virtual machine from Linode:
DELETE table
FROM (
SELECT MAX(id) id, field
FROM table
GROUP BY field
) temp_table
INNER ...
- 123
1
vote
0
answers
101
views
Explain partitions
What does the possible keys, reference and rows indicate in explain partitions?
I have used partitions on the table and I executing the explain partitions with a query:
select count(...
- 21
1
vote
1
answer
109
views
How to optimize search restricted by boolean column?
I want to optimize a query1 of the form
SELECT yid, xid FROM x WHERE is_principal;
Here, is_principal is a non-null boolean column of x.
If I run EXPLAIN QUERY PLAN on this query, the output I get ...
- 341
1
vote
0
answers
226
views
Oracle sql developer tree shaped represantation of Explain_Plan
I want to ask what exactly happens to the following Explain_Plan table :
It firsts Joins the two tables Products & Order_Items then the output is joined with Orders and then with the Customers.Am ...
- 75
1
vote
0
answers
93
views
Mysql: Large Query with subqueries optimization
Any query optimization gurus in the house??
Anyway, I am trying to attack this on two fronts, one being to code a way around it but secondly, I'm thinking some real optimization might help in the ...
- 11
1
vote
1
answer
444
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 ...
- 13
1
vote
1
answer
381
views
View the final query executed by PostgreSQL Function
I have a PostgreSQL function with Language plpgsql.
Since the EXPLAIN ANALYZE VERBOSE for my function executed like this:
EXPLAIN ANALYZE VERBOSE SELECT foo()
doesn't seem to log any valuable ...
- 191
0
votes
2
answers
63
views
Why is this Postgres query so slow?
I'm trying to debug the slow query below but I'm struggling to understand why it is slow. I can see that both plan and subplan do an index scan, including an "Index only scan" for the ...
- 191
0
votes
2
answers
59
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 ...
0
votes
1
answer
115
views
On which fields to index? Fields queried (WHERE) or fields returned (SELECT)
I have a large MySQL database table (~1 million rows and growing) on an AWS RDS Medium instance:
mysql> describe clients;
+-----------------+---------------+------+-----+---------+----------------+...
- 1,085