Search Postgresql Archives

Index scan and bitmap index scan - hard to understand how planner chooses

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

 



Here is a script for initializing my database

*drop table if exists z;
create table z as select lpad(i::text,6,'0') as name from
generate_series(0,999999) as i;
create index z_name on z(name text_pattern_ops);
analyze;
*
So - I have a table z with one column name that has textual index.

Now, I want to execute like queries against this table and see which plan is
used.

*explain select * from z where name like '0000%'*
Index Scan using z_name on z  (cost=0.00..8.38 rows=100 width=7)
  Index Cond: ((name ~>=~ '0000'::text) AND (name ~<~ '0001'::text))
  Filter: (name ~~ '0000%'::text)

*explain select * from z where name like '1111%'*
Bitmap Heap Scan on z  (cost=5.21..304.15 rows=100 width=7)
  Filter: (name ~~ '1111%'::text)
  ->  Bitmap Index Scan on z_name  (cost=0.00..5.19 rows=83 width=0)
        Index Cond: ((name ~>=~ '1111'::text) AND (name ~<~ '1112'::text))

*explain select * from z where name like '5555%'*
Index Scan using z_name on z  (cost=0.00..8.38 rows=100 width=7)
  Index Cond: ((name ~>=~ '5555'::text) AND (name ~<~ '5556'::text))
  Filter: (name ~~ '5555%'::text)

*explain select * from z where name like '7777%'*
Bitmap Heap Scan on z  (cost=7.87..354.01 rows=10101 width=7)
  Filter: (name ~~ '7777%'::text)
  ->  Bitmap Index Scan on z_name  (cost=0.00..5.34 rows=98 width=0)
        Index Cond: ((name ~>=~ '7777'::text) AND (name ~<~ '7778'::text))

*explain select * from z where name like '9999%'*
Index Scan using z_name on z  (cost=0.00..8.38 rows=100 width=7)
  Index Cond: ((name ~>=~ '9999'::text) AND (name ~<~ '999:'::text))
  Filter: (name ~~ '9999%'::text)


So - absolutely cannot understand this.
Why it chooses plan in such a haotic way ?
Is using bitmap index hurts performance there ?




--
View this message in context: http://postgresql.1045698.n5.nabble.com/Index-scan-and-bitmap-index-scan-hard-to-understand-how-planner-chooses-tp5760304.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[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