29
votes
EXPLAIN ANALYZE shows no details for queries inside a plpgsql function
The correct syntax for the EXPLAIN call needs a SELECT. You can't just write the bare function name in SQL:
EXPLAIN ANALYZE SELECT f1();
Optimization
PL/pgSQL functions are black boxes to the query ...
- 159k
26
votes
Accepted
Why is count(*) slow, when explain knows the answer?
Explain is using previously gathered statistics (used by the query optimizer). Doing a select count(*) reads EVERY data block.
Here's a cheap way to get an estimated row count:
SELECT table_rows
FROM ...
- 568
25
votes
Accepted
what is "planSummary: IDHACK"?
It is just informing that mongod has chosen to use the _id index.
More specifically: IDHACK means that normal query plan path (evaluation, caching, re-evaluation) was bypassed for the query shape {...
- 5,453
18
votes
Accepted
Selecting top 10 from indexed field of a big table takes too long
In your sample code above, the index is explicitly created as NULLS LAST and the query is implicitly running NULLS FIRST (which is the default for ORDER BY .. DESC) so PostgreSQL would need to re-...
- 306
12
votes
Accepted
Why does a SELECT statement dirty cache buffers in Postgres?
This has a simple reason.
In PostgreSQL a row has to go through a visibility check. On the first read, the system checks if a row can be seen by everybody. If it is, it will be "frozen". This is ...
- 1,160
12
votes
Accepted
Clustered Index Scan (Clustered) vs. Index Seek (NonClustered) + Key Lookup (Clustered)
Because the majority of the table fits the criteria for the first query, so it is more efficient to scan the clustered index rather than do key lookups for each of the rows that match the criteria.
...
- 2,136
11
votes
Accepted
What does "Heap Block" in "Bitmap Heap Scan" mean?
Bitmaps can either store a bitmap of rows, or if that becomes too large to fit in work_mem it can "go lossy" by storing a bitmap of blocks. It can do this selectively, so some blocks can be converted ...
- 34.3k
10
votes
Accepted
Postgres EXPLAIN ANALYZE is super slow
If you only want to look at the execution plan, run EXPLAIN without ANALYZE.
ANALYZE will cause the query to be executed, which provides much more useful data, but takes as long as the query runs.
- 36.9k
8
votes
Accepted
Width in Postgres EXPLAIN?
Answer is in the article you are referring to:
Here the planner has decided to use a two-step plan: the child plan
node visits an index to find the locations of rows matching the index
...
- 2,338
7
votes
Accepted
MongoDB explain winning plan shows "EOF"
Ok, I found out why. Apparently I was querying against the admin database. Always run use <db> guys
- 163
7
votes
Accepted
Non-deterministic performance of query on select, from 1s to 60s on table with 1 billion rows
Explanation
The all-important difference between the two query plans is the added read=xyz bit in multiple places of the slow version.
Slow:
Buffers: shared hit=116296 read=42298
Fast:
Buffers: ...
- 159k
6
votes
Accepted
conditional subquery
There are three important parts to reading query plans here,
Did it run. If so,
How many times?
Was it correlated?
Sample Data
You didn't provide any sample data, so let's create some.
CREATE ...
- 58k
6
votes
conditional subquery
Evan already pointed out that you may have overlooked the (never executed) in the output of EXPLAIN ANALYZE.
A cleaner and more versatile way to write your query in modern Postgres would be with a ...
- 159k
6
votes
Accepted
What's actually happening in the "Result" node and "Append" in a Postgres plan?
The Append has to do some buffer management. It also makes a lot of calls to some clock function (gettimeofday, for example), to satisfy the timing component of EXPLAIN ANALYZE. That overhead might ...
- 34.3k
6
votes
Accepted
Does SQL Server Management Studio 2017 Contain a New Explain Feature
No, not a feature - it's turning one of these on:
SET SHOWPLAN_TEXT ON;
SET STATISTICS PROFILE ON;
The SHOWPLAN_TEXT option and STATISTICS PROFILE option are well-documented and have been around ...
- 42.1k
5
votes
Accepted
What do the shared buffers metrics for various types of index scans represent?
In this case, it's doing an Index Scan. As best as I know, the difference between an Index scan and a Bitmap Index/Heap Scan is that the former will read pages in the order defined by the index, while ...
- 53.2k
5
votes
Why am I getting `Impossible WHERE noticed after reading const tables` in explain query?
MySql Explain uses the values you provide, literally, to traverse rows of the associated tables. If you provide a constant/key value which is not in the associated table, MySql Explain will stop with ...
- 51
5
votes
Why is count(*) slow, when explain knows the answer?
Explain gets the number from some "statistics" that are used to estimate things for the Optimizer. That number can be far from correct -- I sometimes see it being more than a factor of 2 (higher or ...
- 71.6k
5
votes
Accepted
What does "Rows Removed by Index Recheck" without "lossy" mean?
Some index strategies don't definitely conclude that a tuple meets the criteria. They can definitely and rapidly eliminate most tuples which provably can't meet the criteria (which is where the ...
- 34.3k
4
votes
Accepted
Unit of COST in an Oracle execution plan
Unfortunately the answers are as follows:
Is there a unit for cost in an Oracle execution plan?
Not really.
I mean if the cost of an operation is 50 then can I map this number to CPU cycles or ...
- 9,741
4
votes
Accepted
Monitoring the Postgres query parser
It's not possible to save query plans "for all future sessions". Query plans are only ever saved for the current session. And only reused under a number of favorable conditions.
Plans for ad-hoc SQL ...
- 159k
4
votes
Accepted
Index not used on partitioned table
There are a few problems here:
First, as jkavalik says in the comments on the OP, the order of columns in an index matters. Basically, in your case for index_rqcd to be used for filtering on rq_date,...
- 690
4
votes
Accepted
Slow Performance On Update SQL Server
You've got some code, somewhere, converting your ID to an NVARCHAR(32) field. I don't know how or why this is happening, but that's causing it to ignore your index, resulting in a full scan instead of ...
- 3,585
4
votes
Accepted
Why is AWS RDS' maximum auto_explain.log_min_duration parameter 214783?
This is not specific to AWS RDS (well part of it specific to that), it is in the upstream code.
The setting is restricted to INT_MAX / 1000. But I can't figure out why, as that setting is never ...
- 34.3k
4
votes
Accepted
Is non-dynamic scalar subquery evaluted for each row?
It is evaluated once.
EXPLAIN ANALYZE will display lines like these:
...
InitPlan 1 (returns $0)
-> Index Scan using t2_pkey on t2 (cost=0.29..5.31 rows=1 width=4) (actual time=0.024..0.025 ...
- 159k
4
votes
Accepted
Does a huge key length value for a mulibyte column affect the index performance?
The number is a worst-case estimate. Remember that EXPLAIN doesn't typically read the data, so it has no idea if your data contains only a short string of 1-byte characters on average, or if it ...
- 11.1k
4
votes
Accepted
Complex `where` dramatically affects query performance
There are two things that jump out immediately with the information_schema Details:
MyISAM
differing collations
Regarding MyISAM
When there is a JOIN between MyISAM and InnoDB, the InnoDB tables ...
- 1,873
3
votes
How to optimize MySQL SUM query even after adding index
Try Summing in a separate query and then joining results:
SELECT
a.RecoveryCaseID,
a.PatientClientID,
a.ClientID,
a.ClientName,
a.CaseOpenedDate,
a.CaseCloseDate,
a.CaseType,
a.CaseStatus,
a....
- 2,338
3
votes
Accepted
Mysql Index for concat select
Assuming that card_no and log_entry have VARCHAR or CHAR type, I would first add an index on (card_no, date, last_entry):
ALTER TABLE entry_log
ADD INDEX card_no__date__last_entry__ix
(card_no, ...
- 94.3k
3
votes
Mysql Index for concat select
Cleanse your data before storing it. Otherwise, INDEXes may be useless.
I this particular case, the TRIM function is hiding card_no, making the INDEX on card_no useless.
This SELECT would run a lot ...
- 71.6k
Only top scored, non community-wiki answers of a minimum length are eligible