Re: Show lossy heap block info in EXPLAIN ANALYZE for bitmap heap scan
От | Amit Khandekar |
---|---|
Тема | Re: Show lossy heap block info in EXPLAIN ANALYZE for bitmap heap scan |
Дата | |
Msg-id | CACoZds0A=_YrhJDvmELQ_8NovdPbYNAq=dsrPp14-H0ixV2bAg@mail.gmail.com обсуждение исходный текст |
Ответ на | Show lossy heap block info in EXPLAIN ANALYZE for bitmap heap scan ("Etsuro Fujita" <fujita.etsuro@lab.ntt.co.jp>) |
Ответы |
Re: Show lossy heap block info in EXPLAIN ANALYZE for bitmap heap scan
("Etsuro Fujita" <fujita.etsuro@lab.ntt.co.jp>)
|
Список | pgsql-hackers |
On 25 November 2013 13:37, Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> wrote:
Reconsidering that, I wish to know your opinion. The patch shows the number of exact/lossy pages that has been fetched in a bitmap heap scan. But the number varies with the fraction of tuples to be retrieved like the following.------------------------------------------------------------------------------------------------------------------------------------
postgres=# EXPLAIN ANALYZE SELECT * FROM demo WHERE col2 between 0.01 and 0.02;
QUERY PLAN
Bitmap Heap Scan on demo (cost=2187.35..101419.96 rows=102919 width=42) (actual time=23.684..1302.382 rows=99803 loops=1)Recheck Cond: ((col2 >= 0.01::double precision) AND (col2 <= 0.02::double precision))Rows Removed by Index Recheck: 6279502
Heap Blocks: exact=1990 lossy=59593
-> Bitmap Index Scan on demo_col2_idx (cost=0.00..2161.62 rows=102919 width=0) (actual time=23.330..23.330 rows=99803 loops=1)Index Cond: ((col2 >= 0.01::double precision) AND (col2 <= 0.02::double precision))Total runtime: 1311.949 ms
(7 rows)
postgres=# EXPLAIN ANALYZE SELECT * FROM demo WHERE col2 between 0.01 and 0.02 LIMIT 5000;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=2187.35..7008.26 rows=5000 width=42) (actual time=23.543..86.093 rows=5000 loops=1)
-> Bitmap Heap Scan on demo (cost=2187.35..101419.96 rows=102919 width=42) (actual time=23.542..85.196 rows=5000 loops=1)Recheck Cond: ((col2 >= 0.01::double precision) AND (col2 <= 0.02::double precision))Rows Removed by Index Recheck: 312179
Heap Blocks: exact=99 lossy=2963
-> Bitmap Index Scan on demo_col2_idx (cost=0.00..2161.62 rows=102919 width=0) (actual time=23.189..23.189 rows=99803 loops=1)Index Cond: ((col2 >= 0.01::double precision) AND (col2 <= 0.02::double precision))Total runtime: 86.626 ms
(8 rows)
So, my question is, we should show the number of exact/lossy pages in a TIDBitmap, not the number of these pages that has been fetched in the bitmap heap scan?
Yes, I agree that rather than looking at the bitmap heap scan to track the number of pages, we should look somewhere in the underlying index scan. Yes, we should get a constant number of index pages regardless of the actual parent table rows. I can see that btgetbitmap() adds all the tuples into the bitmap, so somewhere below under btgetbitmap() might be the right place to track. Somewhere in tbm_create_pagetable(), but not sure.
Thanks,
Best regards,
Etsuro Fujita
В списке pgsql-hackers по дате отправления:
Следующее
От: "Etsuro Fujita"Дата:
Сообщение: Re: Show lossy heap block info in EXPLAIN ANALYZE for bitmap heap scan