From: | Oleksii Kliukin <alexk(at)hintbits(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Álvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: rows estimate in explain analyze for the BRIN index |
Date: | 2015-12-30 17:28:21 |
Message-ID: | FB871749-F65D-439D-8AC3-D13550595BE5@hintbits.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
> On 30 Dec 2015, at 17:44, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Oleksii Kliukin <alexk(at)hintbits(dot)com> writes:
>>> On 30 Dec 2015, at 17:02, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Another idea would be to use the heap's row density as calculated
>>> by the last ANALYZE (ie, reltuples/relpages), with a fallback to 100
>>> if relpages=0. This'd only be convenient if the bitmap scan node has
>>> the parent heap rel open, which it might not.
>
>> +1
>
> Any objections to the attached?
Looks good to me. On my sample system with 100K rows, the new version gives me:
— CREATE TABLE test AS SELECT id FROM generate_series(1,100000) id;
— CREATE INDEX ON test USING brin(id);
postgres=# explain analyze select 1 from test where id = 500;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=12.01..16.02 rows=1 width=0) (actual time=0.199..4.220 rows=1 loops=1)
Recheck Cond: (id = 500)
Rows Removed by Index Recheck: 28927
Heap Blocks: lossy=128
-> Bitmap Index Scan on test_id_idx (cost=0.00..12.01 rows=1 width=0) (actual time=0.072..0.072 rows=28800 loops=1)
Index Cond: (id = 500)
Planning time: 0.433 ms
Execution time: 4.323 ms
(8 rows)
which is much closer to the actual number of rows removed by the index recheck + the one left.
--
Oleksii
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2015-12-30 17:30:43 | Re: Some 9.5beta2 backend processes not terminating properly? |
Previous Message | Andres Freund | 2015-12-30 17:23:16 | Re: Some 9.5beta2 backend processes not terminating properly? |