Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [explain]

The EXPLAIN statement can be used to obtain information about the database executes a statement.

0
votes
1answer
22 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
52 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
31 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
34 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
70 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
14 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
24 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
18 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 ...
0
votes
0answers
37 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
51 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 ...
4
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
81 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: ...
2
votes
1answer
385 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
47 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 = '...
0
votes
1answer
229 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
31 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
56 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
667 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
625 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
213 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
36 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
23 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
53 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
209 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
38 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
217 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
680 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
102 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
85 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
68 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
129 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
489 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 ...
3
votes
1answer
307 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
385 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 ...
8
votes
2answers
6k 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
44 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
262 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
91 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
123 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 ...
0
votes
1answer
207 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(...
2
votes
1answer
4k views

what is “planSummary: IDHACK”?

This Query scans only one document and returns only one document. But this is very slow: 2017-05-22T07:13:24.548+0000 I COMMAND [conn40] query databasename.collectionname query: { _id: ObjectId('...
0
votes
0answers
260 views

Comparing explain plains and execution time

Please consider the following 2 queries (which yield the same result), and their corresponding explain plan output: Query A: select max(registro) from telemetria_data.historico_353871042915279 ...
5
votes
1answer
421 views

What's actually happening in the “Result” node and “Append” in a Postgres plan?

I have a table (postgres 9.6) which is partitioned by date into about 70 child tables. The EXPLAIN output below is truncated as most of it is identical bitmap index/heap scans of each of the child ...
0
votes
2answers
503 views

Evaluate different EXPLAINs on Redshift

I'm trying to understand EXPLAIN on Redshift. I'm in a case were I have data like this id | user_id | created_at ---|--------------------------------- 1 | 1 | 2017-02-08 14:32:10.96 2 | 1 ...
2
votes
2answers
5k views

conditional subquery

I have the following query: SELECT id, email, first_name as "firstName", last_name as "lastName", is_active as "isActive", password, access, CASE WHEN access < 3 THEN ( ...
0
votes
2answers
48 views

other tools/methods to investigate slow query

I have a query with a subquery, both are fast but when combined they take forever (meaning whatever time above what is acceptable in my application). I tried using 'explain' to see what's going on, it ...
2
votes
1answer
2k views

How to get the size of the result of a SQL query?

How do you get the size (in bytes) of the result of a SQL query, in MySQL 5.6? EXPLAIN will return the number of fetched rows, but not their size. My aim is to evaluate common queries to know a ...