Re: Bitmap Heap Scan and Bitmap Index Scan

Hi

so 15. 9. 2018 v 9:39 odesílatel Arup Rakshit <ar(at)zeit(dot)io> napsal:

> Here is a explain plan of a very simple query:
>
> aruprakshit=# explain analyze select first_name, last_name from users
> where lower(state) = 'colorado';
> QUERY PLAN
>
> ----------------------------------------------------------------------------------------------------------------------------------
> Bitmap Heap Scan on users (cost=5.86..161.40 rows=203 width=13) (actual
> time=0.134..0.444 rows=203 loops=1)
> Recheck Cond: (lower((state)::text) = 'colorado'::text)
> Heap Blocks: exact=106
> -> Bitmap Index Scan on lower_state_users_idx (cost=0.00..5.81
> rows=203 width=0) (actual time=0.098..0.098 rows=203 loops=1)
> Index Cond: (lower((state)::text) = 'colorado'::text)
> Planning time: 0.263 ms
> Execution time: 0.517 ms
> (7 rows)
>
> I read this
> https://www.postgresql.org/message-id/12553.1135634231%40sss.pgh.pa.us
> <https://www.postgresql.org/message-id/12553.1135634231@sss.pgh.pa.us>
> and
> https://www.postgresql.org/message-id/464F3C5D.2000700%40enterprisedb.com
> <https://www.postgresql.org/message-id/464F3C5D.2000700@enterprisedb.com> to
> understand what this bitmap heap scan and index scan is. But there are some
> questions still in mind which I am not able to figure out yet.
>
> Does bitmap index apply when normal index scan is costly?
>

yes

Does bitmap index always store page number of matching tuples instead of
> just the tuples?
>

What I know, it doesn't store tuples - if there are good enough memory,
then tid are stored (page number, tuple number), else only page numbers are
stored.

> What is Heap Blocks: exact=106 ?
>

see
https://paquier.xyz/postgresql-2/postgres-9-4-feature-highlight-lossyexact-pages-for-bitmap-heap-scan/

Why the cost is higher in Heap scan than index scan?
>

It have to read all pages, but depends on hw and configuration, this read
can be fast

Regards

Pavel

> Thanks,
>
> Arup Rakshit
> ar(at)zeit(dot)io
>
>
>
>