Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.

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
1answer
36 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
26 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
18 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
22 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
20 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
29 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
26 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
121 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
53 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
49 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
180 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; ...
0
votes
0answers
16 views

MySQL query is slow - Need to understand EXPLAIN output

Dear Database Administrators community, I have a query which I would like to find out what causes it to be slow and what can I do to improve it. This is the query I am using: select ereturn1_....
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
68 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
64 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
52 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
114 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
531 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
69 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
354 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
38 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
84 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
1k 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
1k 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" }...
2
votes
1answer
329 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
37 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
25 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
57 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
296 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
21 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 ...
1
vote
1answer
272 views

Why is AWS RDS' maximum auto_explain.log_min_duration parameter 214783?

That seems like a really arbitrary number of milliseconds to use as a maximum. What can I do if I want to log even longer-running queries on a data warehouse? C.f. https://collabshot.com/show/83dd1e
0
votes
1answer
31 views

Is indexing the following columns efficient?

I have the table: orders with the following columns (orderNumber, orderDate, shippedDate, status, users_userId) I indexed orderDate, shippedDate, status for performing similar queries to the ...
7
votes
2answers
702 views

Selecting top 10 from indexed field of a big table takes too long

I have a table with 165M records like this: Performance id integer installs integer hour timestamp without time zone I also have an index on hour: CREATE INDEX hour_idx ON ...
0
votes
2answers
110 views

Select query taking too long MYSQL 5.6.17

I have the below select and can't understand why it's taking about an hour to give me the result. SELECT (CASE WHEN a.ACODE='000A' THEN c.datfinprel WHEN a.ACODE='000T' THEN c.datiniprel ...
1
vote
0answers
136 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 ...
2
votes
0answers
83 views

Why do queries on two tables with the same structure have different execution times

I have partitioned a large table in a PostgreSQL database into monthly child tables with the same structure and similar numbers of rows. Recently I have noticed that the newer tables take much longer (...
0
votes
1answer
216 views

Why PostGIS GIST index is not used? [closed]

I am using PostGIS 2.2.1 with PostgreSQL 9.5.10. I have a table with a geography field for coordinates and I am making a simple bounding box query. The table has 1029 rows. EXPLAIN says it does a ...
1
vote
1answer
754 views

What's the difference between DISTINCT_SCAN and IXSCAN in explain output?

In Mongo 3.4 explain("executionStats").distinct(...), for some queries the winning plan contains an IXSCAN step, and for other queries it contains an DISTINCT_SCAN step. What is the difference ...
2
votes
1answer
381 views

How to benchmark query parsing & planning in Postgres?

I'm trying to benchmark the query planning and parsing steps in PG: However, it seems that the query planner is using some sort of cache - only the first execution of EXPLAIN ANALYZE seems to report ...
1
vote
0answers
502 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 ...
12
votes
2answers
10k views

Why is count(*) slow, when explain knows the answer?

This query: select count(*) from planner_event takes a very long time to run - so long, I gave up and killed it before it finished. However, when I run explain select count(*) from planner_event, I ...
1
vote
2answers
70 views

Given the following tables/index/explain planning, why is the query so slow?

I have the following tables: CREATE TABLE trace ( uuid uuid NOT NULL, result text, ruleid uuid, previousruleresultid uuid, senderduns text, receiverduns text, eventid uuid, ...
2
votes
1answer
48 views

MySQL explain results different after adding and then removing index

Running into some odd behavior. I have two tables product and product_description with the following structures (some irrelevent columns omitted for brevity): CREATE TABLE `oc_product` ( `...
3
votes
2answers
303 views

Create an explain plan for query over a global temporary table

I have a query which aggregates data from a global temporary table. The table will be filled right before the aggregation. My goal is a reliable explain plan for that query. Is there a way in ...
2
votes
1answer
104 views

What is the difference between select from function and select function

I am doing a test with generate_series function on Postgres v.9.6 on Ubuntu 16.04. So, I get a curiosity about loops (loops=100000) when calling that function as below. EXPLAIN ANALYZE SELECT count(...
1
vote
1answer
139 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 ...
1
vote
1answer
312 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(...