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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general