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
1answer
10 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
17 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
28 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
27 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
29 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
17 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
63 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
39 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
18 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
77 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
45 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
103 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
73 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
28 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
37 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
31 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 ...
0
votes
1answer
178 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
570 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
88 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
107 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
89 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
54 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
21 views

Interpreting explain plan timing, A-Rows, and E-Rows with nested loops

I got the below output from an explain plan on a very poor-performing query: ---------------------------------------------------------------------------------------------------------------------------...
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
30 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
29 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
255 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, ...
0
votes
1answer
346 views

Help understanding mongodb explain results

I have an index on field f1. {f1:1} Running an explain on the following query db.coll.find() says that the winning plan is a CollScan (expected) However, for db.coll.find().sort({f1:1}) the ...
0
votes
3answers
76 views

MySQL why EXPLAIN result is confusing?

Below is my Query: select * from ( SELECT * from Player where Country_Name in (1,2) ) as A inner join Country C ON A.Country_Name=C.Country_Id; ...
4
votes
1answer
308 views

Postgres Row Level Security policy optimizes poorly compared to inline version

I have a query which looks like this: SELECT post.id, post.author_id, post.published_at, post.content FROM post WHERE post.group_id = 1 ORDER BY post.published_at DESC, post.id LIMIT 5; ...
2
votes
0answers
27 views

Is it possible to replace count distinct inside Correlated sub-queries?

I'm trying to optimize the query bellow. The execution Plan is showing multiple sorting before aggregations (I think this is due to the count distincts in the correlated sub-queries)... Is it ...
0
votes
1answer
106 views

how to create an explain plan for the below query? I'm getting ORA-00900: invalid SQL statement error

I'm getting ORA-00900: invalid SQL statement error when i tried. but the sql query is running fine without explain. Query: create or replace view TaskView as select T1.PersonID,T2.ID ,T2.Task from ...
1
vote
0answers
86 views

explain on view shows derived while explain on select shows simple select type

I have a view v which involves a couple of sub-selects in its SELECT clause plus a couple of joins by id or indexed column. The view query is like: SELECT col1, col2, (SELECT ... FROM subt) AS col3 ...
1
vote
0answers
53 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 ...
3
votes
1answer
1k views

Does SQL Server Management Studio 2017 Contain a New Explain Feature

TL;DR I happened upon a feature in SQL Server Management Studio v17.9 which I was not aware of. It looks a bit like the EXPLAIN found in Oracle, PostgreSQL and MySQL. Where is this feature documented?...
2
votes
1answer
188 views

Why does a SELECT (JOIN on regex) gets 30x slower with +1 row (query plan stays the same)? Lack of memory?

Here is my query, it takes 2.7 sec: SELECT t2.id, t1.id FROM sample t1 INNER JOIN (select * from regex_set limit 33) t2 ON t1.data ~* t2.regex; The associated EXPLAIN (ANALYSE,BUFFERS) output: ...
3
votes
1answer
678 views

MySQL Workbench Visual Explain not working with MariaDB 10.3.9

I recently installed MySQL Workbench 8.0.12 and MariaDB 10.3.9. I created my database and want to start fine tuning indexes and views. I can run a query with the EXPLAIN button and get the results in ...
1
vote
1answer
96 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 = '...
-1
votes
1answer
598 views

MySQL/MariaDB: Comparing performance between two queries

I am trying to compare the performance between two queries which give otherwise the same results. The idea is that I want to compare using Windowing Functions available to newer versions to faking ...
1
vote
0answers
42 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 ...
0
votes
1answer
101 views

Query to identify bad indexes and/or no indexes (on tables with lots of records) takes 4-6 minutes to execute

Query: SELECT Concat(t.table_schema, '.', t.table_name), t.table_rows, snu.non_unique, smax.cardinality, ( t.table_rows / Ifnull(smax.cardinality, 1) ) AS ...
2
votes
2answers
2k views

How to accurately measure query time in PostgreSQL (because EXPLAIN ANALYZE does not seem to do it)?

I know that EXPLAIN ANALYZE returns the query time, but it does not appear to do so for my use case. (Unless I am missing something...what could it be...?) SELECT cars.*, users.*, makers.*, places.* ...
2
votes
2answers
2k views

MongoDB explain winning plan shows “EOF”

I've been working on optimizing my queries on some 2 million documents in MongoDB and I tried to use explain on aggregate functions, but it would display "winningPlan" : { "stage" : "EOF" }...
3
votes
1answer
469 views

What does “Rows Removed by Index Recheck” without “lossy” mean?

Here's part of an EXPLAIN ANALYZE from Postgres 9.6: -> Bitmap Heap Scan on cities (cost=90.05..806.49 rows=265 width=4) (actual time=4.733..45.772 rows=17 loops=1) Recheck Cond: (...
0
votes
1answer
39 views

Postgresql, very long joins

I have two tables Places and Redemptions (details in gist) https://gist.github.com/kunashir/2648e6e4d8a97aa2d03859b8ee08a394 And the query takes a lot of time: SQL (26910.2ms) SELECT "redemptions"....
1
vote
1answer
29 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 ...
2
votes
1answer
60 views

Is non-dynamic scalar subquery evaluted for each row?

Is a scalar subquery that's independent on parent query (does not refer to the parent table) evaluated only once, or for each row, regardless? SELECT /* ... */ FROM t1 WHERE parent = ( ...
1
vote
1answer
385 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 ...
0
votes
0answers
23 views

Simple query rephrasing for optimization

The following query: select exists(select id from avl_adc_equipo cfg where cfg.imei=354676052451142 and cfg.adc=2) Executes pretty fast, but as it ...
0
votes
1answer
39 views

New query taking over 60% of running time

As part of a daily automated reporting system, we run a series of queries to check different operating parameters from our customers' assets tables. Last Tuesday I added a new check that works, but ...