Search Postgresql Archives

Re: Bitmap Heap Scan and Bitmap Index Scan

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi

so 15. 9. 2018 v 9:39 odesílatel Arup Rakshit <ar@xxxxxxx> 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 and https://www.postgresql.org/message-id/464F3C5D.2000700%40enterprisedb.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 ?
 

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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux