From: | Oleksii Kliukin <alexk(at)hintbits(dot)com> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | rows estimate in explain analyze for the BRIN index |
Date: | 2015-12-30 14:49:47 |
Message-ID: | 036AD150-4377-49B6-B93D-A8DAF5FC0BEE@hintbits.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi,
While experimenting with BRIN on PostgreSQL 9.5RC1 I came across the following plan (which is, btw a very good example of how BRIN rocks for the clustered data, the size of this table is around 90GB, the size of the index is around 3MB):
explain (analyze, buffers, verbose) select 1 from example where event_time = now() - interval '5 months';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on example (cost=744.44..757.64 rows=6 width=0) (actual time=73.895..73.895 rows=0 loops=1)
Output: 1
Recheck Cond: (example.event_time = (now() - '5 mons'::interval))
Rows Removed by Index Recheck: 4030
Heap Blocks: lossy=128
Buffers: shared hit=629
-> Bitmap Index Scan on example_event_time_idx1 (cost=0.00..744.41 rows=6 width=0) (actual time=70.335..70.335 rows=1280 loops=1)
Index Cond: (example.event_time = (now() - '5 mons'::interval))
Buffers: shared hit=501
Planning time: 0.125 ms
Execution time: 73.943 ms
(11 rows)
Time: 74.642 ms
Here EXPLAIN ANALYZE reports 1280 rows from the Bitmap Index Scan, but on the higher level, 4030 rows were removed by Index Recheck.
The questions are:
- how does it get 1280 rows from the BRIN index scan, given that BRIN only stores pointers to the heap blocks, not individual rows. Does it calculate the number of rows in the blocks returned?
- how comes that the subsequent recheck filters out 4030 rows, out of supposedly 1280 returned?
Kind regards,
--
Oleksii
From | Date | Subject | |
---|---|---|---|
Next Message | Aleksander Alekseev | 2015-12-30 14:56:31 | Re: Patch: fix lock contention for HASHHDR.mutex |
Previous Message | Alvaro Herrera | 2015-12-30 14:49:13 | Re: Patch: fix lock contention for HASHHDR.mutex |