Hi so 15. 9. 2018 v 9:39 odesÃlatel Arup Rakshit <a...@zeit.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.1135634...@sss.pgh.pa.us> > and > https://www.postgresql.org/message-id/464F3C5D.2000700%40enterprisedb.com > <https://www.postgresql.org/message-id/464f3c5d.2000...@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 > a...@zeit.io > > > >
