CODEWITHSUNDEEP
postgresqlsql-execution-plan
Tim
Tim

Reputation: 99418

What does "Recheck Cond" in Explain result mean?

From an example in PostgreSQL document:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND stringu1 = 'xxx';
                                  QUERY PLAN
------------------------------------------------------------------------------
 Bitmap Heap Scan on tenk1  (cost=5.04..229.43 rows=1 width=244)
   Recheck Cond: (unique1 < 100)
   Filter: (stringu1 = 'xxx'::name)
   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101
 width=0)
         Index Cond: (unique1 < 100)

Am I correct that

Since Bitmap Index Scan already checks the Index Cond on unique1 < 100, why is there "Recheck Cond" on the same condition again in Bitmp heap Scan? What does "Recheck Cond" mean?

I am not sure I understand this related post https://dba.stackexchange.com/questions/106264/recheck-cond-line-in-query-plans-with-a-bitmap-index-scan

Thanks.

Upvotes: 48

Views: 19954

Answers (2)

user330315
user330315

Reputation:

This was explained by Tom Lane on the mailing list:

what is "Recheck condition" and why is it needed?

If the bitmap gets too large we convert it to "lossy" style, in which we only remember which pages contain matching tuples instead of remembering each tuple individually. When that happens, the table-visiting phase has to examine each tuple on the page and recheck the scan condition to see which tuples to return.

Upvotes: 54

Laurenz Albe
Laurenz Albe

Reputation: 246453

It is a potential re-check of the condition that is not always performed.

Only if the bitmap is lossy (which EXPLAIN (ANALYZE) will indicate) the recheck is performed.

A bitmap index scan becomes lossy if work_mem is not big enough to contain a bitmap that contains one bit per table row. It will then degrade to one bit per 8K page. Rows from such blocks will have to be rechecked.

Upvotes: 37

Related Questions