Thread: Index scan and bitmap index scan - hard to understand how planner chooses
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.
Re: Index scan and bitmap index scan - hard to understand how planner chooses
From
boraldomaster
Date:
This seems to be fixed in 9.2. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Index-scan-and-bitmap-index-scan-hard-to-understand-how-planner-chooses-tp5760304p5760637.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: Re: Index scan and bitmap index scan - hard to understand how planner chooses
From
Jeff Janes
Date:
On Mon, Jun 24, 2013 at 5:42 AM, boraldomaster <boraldomaster@gmail.com> wrote:
This seems to be fixed in 9.2.
Which version were you originally seeing it in? I still see that behavior (or something close to it) in 9.4dev.
It stabilizes after doing a "vacuum analyze" (not just "analyze"), but that is because it then switches to Index Only Scan.
I haven't pinned it down exactly, but it seems that there are two issues.
One is that there are two estimates of the row returned, one for the whole query (which is visible in EXPLAIN, and is spot on) and one for the index patch, which is very wrong for 0000% case (the estimate was 1 row rather than 100, visible only in the debugger or with special logging code added). The other is that the correlation between index/column order and table order is thought to be 0 when it is really 1.
Cheers,
Jeff
Re: Index scan and bitmap index scan - hard to understand how planner chooses
From
boraldomaster
Date:
I firstly used 9.1. After switching to 9.2. - the problem was fixed even without index only scan. I added another column to my table so this wasn't index only, but still had better index behaviour. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Index-scan-and-bitmap-index-scan-hard-to-understand-how-planner-chooses-tp5760304p5760677.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.