Stack Exchange Network

Stack Exchange network consists of 174 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
0answers
23 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
50 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?...
0
votes
0answers
19 views

How to optimize (OpenStreetMap) map tiles rendering?

I'm trying to optimize my OpenStreetMap map tiles rendering. This is one of the queries renderd makes to PostgreSQL: SELECT ST_AsBinary("way") AS geom, "type" FROM (SELECT way, ...
1
vote
0answers
64 views

Adding field in SELECT cause Using temporary; Using filesort [closed]

I have query with JOIN EXPLAIN SELECT o.id, ostring.services_string FROM offers o LEFT JOIN offers_string ostring ON o.`id` = ostring.`id` WHERE o.`date` >= '2018-09-22 00:00:00' o.`date` <= '...
1
vote
0answers
70 views

6 million shared buffer hit, is that a good thing? [closed]

I have a query which takes around 17 seconds to complete. Here's the EXPLAIN (analyze, buffers): The main part in text is here "-> Index Scan using responses_form on responses (cost=0.55.....
2
votes
1answer
68 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
226 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
30 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
120 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
22 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
42 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
314 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.* ...
1
vote
2answers
348 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
136 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
34 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
21 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
50 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
138 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
19 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
173 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
0answers
53 views

Very slow result for seemingly simple query

I imagine I may lack important details, so please don't hesitate to ask for more info. I have a database that is approximately 130 GB large. One of the tables is "c_feature", see screenshot below. I ...
0
votes
0answers
23 views

Why does this query not use the indexes as I expected?

This is my query in MySQL select ofp.id, wwwbd.id, ofp.sector, wwwpg.id, ofp.severity, wwwus.id, ofp.created_at, ...
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
655 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
96 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 ...
0
votes
0answers
51 views

Explain (analyze,buffer) output in PostgreSQL 9.1

How to get Explain (analyze,buffer) result without manually running it and without the use of 'auto_explain' in PostgreSQL 9.1 of all the queries running in the server
1
vote
0answers
54 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
50 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
86 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
314 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 ...
0
votes
0answers
25 views

Query EXPLAIN output from an SQL

I'd like to know if there's means with MySQL to access the EXPLAIN data from an SQL itself. Basically, I'm looking at mean to have the SQL itself as a column in the EXPLAIN output that I can then use ...
3
votes
1answer
231 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
290 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 ...
5
votes
2answers
4k 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
68 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
41 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
205 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 ...
1
vote
1answer
85 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
111 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
138 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
3k 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
224 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
325 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
345 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
3k 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
42 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 ...
1
vote
1answer
1k 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 ...
1
vote
2answers
3k views

Slow Performance On Update SQL Server

I would like to ask your help. I have very slow update statements on this tables even if the table is only a basic table. I would like to know the reason for the slow update even if what I'm throwing ...