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
