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

From: boraldomaster <boraldomaster(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Index scan and bitmap index scan - hard to understand how planner chooses
Date: 2013-06-21 10:02:11
Message-ID: 1371808931180-5760304.post@n5.nabble.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Eduardo Morras 2013-06-21 10:45:32 Re: Replication with Drop: could not open relation with OID
Previous Message salah jubeh 2013-06-21 09:20:53 Re: Replication with Drop: could not open relation with OID