Hot answers tagged

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 ...
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 ...
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-...
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 ...
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
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: ...
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 ...
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 ...
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 ...
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 ...
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 ...
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, ...
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