Skip to main content

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
0 answers
12 views

MongoDB - optimizing compound index by using executionStats

I am visiting the MongoDB learn DBA course. In one of the labs, it was shown how to optimize the compound index by examining the explain plan. The lab used the sample training dataset. Following query ...
ray's user avatar
  • 126
0 votes
1 answer
68 views

Extremely slow query whenever using ORDER BY, even when an index is present

I've been trying to debug a particularly slow query that never completes (it takes forever and eventually timeout), and found out that it's down to the ORDER BY statement: if it's there, it never ...
laurent's user avatar
  • 191
0 votes
0 answers
42 views

Query performance degrade after upgrade from MySQLv5.6 to v5.7

I upgraded the MySQL Server from v5.6 to v5.7 and since then I see query performance degrade. For example, I have the following select query that selects some columns and has a single column in the ...
msbeast's user avatar
  • 21
0 votes
2 answers
120 views

Finalize GroupAggregate parent using Partial HashAggregate child nodes in Postgres query plan

I have seen in some query plans that the parent is a Finalize GroupAggregate but its child nodes are Partial HashAggregates. When does this make sense? As an example, I have a query that resembles ...
ahron's user avatar
  • 813
0 votes
2 answers
94 views

PostgreSQL plan cache and variable number of arguments

Query: select * from table where col in ( values (?), (?), (?) ) The query text is generated, and number of (?) is variable and up to 10000. It works just fine, just wondering, whether Postgres ...
Mikhail Boyarsky's user avatar
0 votes
1 answer
69 views

how to get explain plan from pl/sql function call?

consider this working pl/sql block: declare o_result boolean; io_error varchar2(30000); tab1 someTable1; tab2 someTable2; begin tab1(1).col1 := 'str'...
gabriel119435's user avatar
1 vote
0 answers
44 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 ...
Sébastien De Wolf's user avatar
0 votes
2 answers
106 views

Cheaper query plan takes much longer to execute

I am running a query like explain (analyze, buffers) select col1, col2, count(col3) as c from table1 group by 2, 1 order by 2, 1 When the work_mem is set at 4 MB, the plan looks like this: "...
ahron's user avatar
  • 813
1 vote
0 answers
35 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, ...
ahron's user avatar
  • 813
0 votes
2 answers
75 views

Query is much slower with indexes

For a select query on a table with a few million rows, I notice that the performance is much slower with indexing. I have replicated the problem on an example table with fewer columns: CREATE TABLE ...
ahron's user avatar
  • 813
0 votes
1 answer
49 views

Is it a red flag to see data typecast in query plan?

In a query plan I see the type of a row being cast. There's an index that should be used, and isn't. Is the typecast a red flag? The table has had an ANALYZE and has 110 million rows. Of these, 10% ...
Joe's user avatar
  • 1,645
0 votes
1 answer
46 views

ORDER BY slows query performance

I see very bad query performance when ORDER BY is added act.activity_schedule is a DB view. There are indexes on date and start_hour. create index activity_schedule_tab_date_index on ...
Robert's user avatar
  • 141
2 votes
0 answers
72 views

Why does the query plan change dramatically and never complete with a single additional where clause?

The following postgres query changes quite a bit by a single additional WHERE condition. std.a133_findings has an index over (audityear, dbkey), std.a133_findings_augmented has uniqueness constraint ...
diyer0001's user avatar
0 votes
1 answer
65 views

mysql min with where clause optimization

I want to get min user_id for a tenant_id. mysql engine - innodb table - users(id, tenant_id, is_deleted, creation_date) pk (id) sk (tenant_id, creation_date) query1 select min(id) from users where ...
user3239193's user avatar
0 votes
2 answers
83 views

MySQL 8 query doesn't prefer index with higher cardinality

I have a query similar to the following: FROM example_table WHERE `date` BETWEEN '2023-11-26' AND '2023-11-28' AND location_id IN (3, 4, 6, 7, 8, 10, 11, 12, 14, 18, 19, 22, 23, 24, 28, 29, ...
Shahid Thaika's user avatar
1 vote
2 answers
67 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 ...
MattB's user avatar
  • 111
0 votes
2 answers
64 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 ...
laurent's user avatar
  • 191
1 vote
1 answer
90 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 ...
Arun's user avatar
  • 13
0 votes
1 answer
41 views

Help optimizing db architecture

I have the following query which takes forever to execute (it never does, if I rewrite it a little it executes, but also takes forever) SELECT DISTINCT pac.product_id, ...
Andrey Deineko's user avatar
0 votes
1 answer
358 views

PostgreSQL Query Performance: Sorting Array Length vs. CTE

I'm having a significant performance difference between two PostgreSQL queries that I'm trying to understand and optimize. I have a table with around 2TB of data, and both queries use the sequential ...
sujeet's user avatar
  • 247
0 votes
0 answers
67 views

How can I improve the performance of a query that relies on data from lots of tables?

I am working on a Web application that renders a food menu in one view. The menu is modelled as one big tree, which contains nodes like services (breakfast, lunch, dinner), courses (starter, main, ...
Check12's user avatar
  • 63
0 votes
1 answer
339 views

Same query but different execution plans for postgresql query

I am struggling with a query running for 30 seconds on one of my db hosts. Same query ( same configuration for db in postgresql.conf ) runs in 3 seconds with a different execution plan. Yes the ...
Nicole's user avatar
  • 1
0 votes
1 answer
44 views

Different index selection and join strategy between QA and Prod environments in PostgreSQL

I have encountered a scenario where the same query on a PostgreSQL database is exhibiting different index selection and join strategies between the QA and Prod environments. I'm trying to understand ...
sujeet's user avatar
  • 247
0 votes
1 answer
453 views

Can sorting in postgresql EXPLAIN plan be prevented?

I have a large table join on the keys. The explain plan shows that most of the time is spent on sorting. Is there anything I can do to prevent the large sort steps? Can I pre-sort the table? Query and ...
Bart Jonk's user avatar
  • 317
0 votes
0 answers
40 views

Help me understand EXPLAIN PLAN of mysql 8

-> Sort row IDs: users.userid (actual time=24875.751..34999.822 rows=2547647 loops=1) -> Table scan on <temporary> (cost=0.00..0.50 rows=6083517) (actual time=0.027..2488.740 rows=...
Sreenivas Kolanthayappan's user avatar
0 votes
0 answers
138 views

PostgreSQL query times out when selecting a smaller amount of data (different query plan)

PostgreSQL 15.2. We have a table like this (simplified): create table behavior.event_59 ( event_id integer not null primary key, second_offset integer not null, -- ... more columns ) ...
EM0's user avatar
  • 190
0 votes
1 answer
515 views

Best way of SELECT on large table with filter to filter out around 1m rows with indexes

I have a table billing_billcycleorders that contains a foreign key billing_cycle_id. There are around 0.9m records for a particular billing_cycle_id. I want to select the data in the chunks for ~5000 (...
sirajalam049's user avatar
2 votes
1 answer
266 views

Different results from Postgres EXPLAIN ANALYZE

I was investigating the performance of a query that hit the top of my list in PMM and noticed strange behavior. The first time the requests execution time is long, subsequent times quickly: Aggregate ...
Марат Бралиев's user avatar
0 votes
0 answers
56 views

PostgreSQL: Python UDF not shown in query plan obtained by EXPLAIN ANALYZE

I have a question regarding the execution of a Python UDF. Let's suppose I have a UDF named testUDF(...) and I apply it to table "testtable". When I run the query SELECT testUDF(...) from ...
KSV97's user avatar
  • 3
0 votes
2 answers
380 views

How to identify sub query in Mariadb Explain

I'm trying to analyze the performance of a complex query in MariaDB (about 15 sub-queries). For that I use the Explain command. The result comes with a lot of derived table (<derived2>, <...
mb14's user avatar
  • 131
0 votes
1 answer
325 views

ANALYZE or EXPLAIN for previous query

EXPLAIN SELECT ... shows the EXPLAIN for a query (without actually running it). SHOW EXPLAIN FOR conn_id shows the EXPLAIN for a query that is currently running. Is it possible to get the EXPLAIN for ...
Nuno's user avatar
  • 829
0 votes
2 answers
138 views

Terrible performance on WP-generated query migrating from MySQL to MariaDB

I'm trying to migrate a website from a WP-focused hosting provider that uses Percona for their DB node to a Jelastic-based provider that offers MariaDB for its DB nodes. There's one query in ...
Titus Toia's user avatar
1 vote
2 answers
309 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 ...
Alessandro Jeanteur's user avatar
0 votes
2 answers
82 views

Oracle query different performances between prod and staging environment

So I have those 2 Oracle 12c databases R2, prod and staging environment, both are perfectly aligned and running on identical hardware. The same select statement will take about 400ms on staging while ...
Marc's user avatar
  • 101
0 votes
1 answer
206 views

How are HashAggregate row estimates calculated?

I have the following query: SELECT foo.* FROM foo WHERE foo."id" IN ((SELECT bar."id" FROM bar WHERE bar.user_id IN ( 100, 102 )) ...
Joe's user avatar
  • 269
0 votes
0 answers
608 views

Postgres - Debugging slow query

we are using postgres 13.7 My company runs a fairly complicated analytical query, unfortunately, I'm not allowed to disclose the query here. The explain plan has around 30 nodes and joins about 10 ...
sha's user avatar
  • 137
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,...
Duru Can Celasun's user avatar
1 vote
1 answer
686 views

Oracle How to get Execution Plan for SQL executed inside of PLSQL?

Is it possible to examine the execution plan for a SQL statement executed inside a PLSQL block? DECLARE l_count PLS_INTEGER; BEGIN SELECT COUNT(1) INTO l_count FROM foo; END; / For ...
Matthew Moisen's user avatar
0 votes
2 answers
516 views

logging EXPLAIN output of slow queries in MySQL

I am trying to understand if there is functionality like auto_explain in PostgreSQL for MySQL. Specifically, I am interested in logging the query plan (not just the query) for all slow queries in ...
karmanaut's user avatar
  • 141
1 vote
1 answer
91 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 ...
User1974's user avatar
  • 1,477
0 votes
1 answer
394 views

Can EXPLAIN be used to get some insights about Common Table Expression (CTE)?

I need to find the ancestors in a table having an ID value and a Google research point me to recursive Common Table Expression (CTE) I wonder if EXPLAIN can be used to get some insights on how MySQL ...
ReynierPM's user avatar
  • 1,796
2 votes
2 answers
2k views

Postgres slow exists subquery

I have a queue system that has two tables, jobs and jobs_queues. jobs is where we put jobs to run and job_queues we populate with queue names so that when we pick jobs we can discard those that are ...
William Oliveira's user avatar
0 votes
1 answer
92 views

Query optimization: LEFT JOIN makes the query incredibly slow

I have the following query with explain analyze and it's incredibly slow because of the LEFT JOIN. EXPLAIN ANALYZE SELECT DISTINCT ON (project_name, release_version, project_id, release_id) vbc.id ...
underachiever's user avatar
1 vote
1 answer
50 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 ...
doppeltesjott's user avatar
0 votes
0 answers
607 views

Different EXPLAIN ANALYZE results for the same query on different servers

I have a table named review with about 33 million rows. I have this query: EXPLAIN ANALYZE (WITH clicker_reviews AS ( SELECT monologue_id FROM review WHERE type = 'CORE' AND ...
Fabien Snauwaert's user avatar
1 vote
0 answers
135 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 ...
Sylvain's user avatar
  • 189
2 votes
2 answers
2k views

Postgresql explain alter table add column doesn't work

I'm using Postgresql 12 (AWS RDS) and pgAdmin4. While the following statement works explain select * from table "Commands"; The next one explain alter table "Commands" drop column &...
Tran Triet's user avatar
0 votes
1 answer
827 views

Postgres changes query plan for prepared statement to something poor after multiple invocations

Postgres 13.4 I have a prepared statement that is similar to this: PREPARE my_statement AS SELECT * FROM products WHERE (normalized_name = $1 OR distinct_normalized_names @> array['$1']) AND ...
Karew's user avatar
  • 182
2 votes
2 answers
2k views

Why does GROUP BY on table A cause "using temporary; using filesort" on table B? [closed]

Given the following contrived example code: SELECT A.author_name , COUNT(T.title) AS num_titles FROM authors A JOIN titles T ON T.authorFK = A.authorPK GROUP BY ...
Codemonkey's user avatar
0 votes
0 answers
21 views

Force PostgreSQL not to think beyond what EXPLAIN says [duplicate]

EXPLAIN gives a good strategy but nevertheless the query (and EXPLAIN ANLALYZE) runs extremely slowly. Is there a simple way to force PostgreSQL always query by the same plan that EXPLAIN produces for ...
porton's user avatar
  • 745

1
2 3 4 5