CODEWITHSUNDEEP
performancepostgresqlindexing
Torge
Torge

Reputation: 2284

Postgres: Index supported query with no results takes awfull long

I have a simple query:

SELECT code FROM subscribers s WHERE s.code = '4012123021';

This code does NOT exist in the databse.

I have an index on code and it is used.

CREATE INDEX ix_subscriber_code ON subscriber USING btree (code COLLATE pg_catalog."default");

I expect this to use nearly no time at all. But it takes 400ms-600ms??

The table has around 400.000 entries. Database has 64Gb Memory. work_mem is set to 512MB. The system is not under load the memory is nearly completely free.

The analyze returns this:

'Bitmap Heap Scan on public.subscriber s  (cost=47.76..4735.80 rows=1979 width=58) (actual time=0.019..0.019 rows=0 loops=1)'
'  Output: code'
'  Recheck Cond: ((s.code)::text = '4019624828'::text)'
'  Buffers: shared hit=3'
'  ->  Bitmap Index Scan on ix_subscriber_code  (cost=0.00..47.27 rows=1979 width=0) (actual time=0.017..0.017 rows=0 loops=1)'
'        Index Cond: ((s.code)::text = '4019624828'::text)'
'        Buffers: shared hit=3'
'Planning time: 0.050 ms'
'Execution time: 0.032 ms'

What is this recheck condition, why is it taking so long after the index scan returned no hit? I am totally puzzled here.

Postgres version is 9.4

Upvotes: 0

Views: 118

Answers (1)

Sami Kuhmonen
Sami Kuhmonen

Reputation: 31153

The recheck condition doesn't take much time at all, as you can see. And this execution at least goes nowhere near 400-600ms.

The bitmap scan finds the rows that match. If there isn't enough memory available it doesn't return just the rows that match but pages that include these rows so after the scan there needs to be a recheck step that checks the returned rows so that they actually match the condition.

The check isn't always actually done even though it is shown in the query plan. It is only needed when memory isn't enough to hold the the results.

Upvotes: 1

Related Questions