Re: rows estimate in explain analyze for the BRIN index

Поиск
Список
Период
Сортировка
От Oleksii Kliukin
Тема Re: rows estimate in explain analyze for the BRIN index
Дата
Msg-id FB871749-F65D-439D-8AC3-D13550595BE5@hintbits.com
обсуждение исходный текст
Ответ на Re: rows estimate in explain analyze for the BRIN index  (Tom Lane <tgl@sss.pgh.pa.us>)
Ответы Re: rows estimate in explain analyze for the BRIN index  (Emre Hasegeli <emre@hasegeli.com>)
Список pgsql-hackers
Дерево обсуждения

On 30 Dec 2015, at 17:44, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Oleksii Kliukin <alexk@hintbits.com> writes:
On 30 Dec 2015, at 17:02, Tom Lane <tgl@sss.pgh.pa.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

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Andres Freund
Дата:
Сообщение: Re: Some 9.5beta2 backend processes not terminating properly?
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Some 9.5beta2 backend processes not terminating properly?