Index scan and bitmap index scan - hard to understand how planner chooses - Mailing list pgsql-general

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.