Bitmap Heap Scan and Bitmap Index Scan

From: Arup Rakshit <ar(at)zeit(dot)io>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Bitmap Heap Scan and Bitmap Index Scan
Date: 2018-09-14 20:34:22
Message-ID: 23F2CFE5-72FA-470C-A84C-0D88D7D2EFC9@zeit.io
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?
Does bitmap index always store page number of matching tuples instead of just the tuples?
What is Heap Blocks: exact=106 ?
Why the cost is higher in Heap scan than index scan?

Thanks,

Arup Rakshit
ar(at)zeit(dot)io

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2018-09-14 21:12:56 Re: Query act different when doing by hand and by using a driver in app
Previous Message Tom Lane 2018-09-14 20:17:39 Re: Code of Conduct plan