Thread: Problem with bitmap-index-scan plan
Hi,
If I run the query
explain analyze select * from ind_uni_100 where a=1 and b=1 and c=1
I get the following plan:
Bitmap Heap Scan on ind_uni_100 (cost=942.50..1411.12 rows=125 width=104)
(actual time=72.556..72.934 rows=116 loops=1)
Recheck Cond: ((c = 1) AND (a = 1) AND (b = 1))
-> BitmapAnd (cost=942.50..942.50 rows=125 width=0) (actual
time=72.421..72.421 rows=0 loops=1)
-> Bitmap Index Scan on index_c_ind_uni_100 (cost=0.00..314.00
rows=50000 width=0) (actual time=21.854..21.854 rows=49832 loops=1)
Index Cond: (c = 1)
-> Bitmap Index Scan on index_a_ind_uni_100 (cost=0.00..314.00
rows=50000 width=0) (actual time=22.371..22.371 rows=50319 loops=1)
Index Cond: (a = 1)
-> Bitmap Index Scan on index_b_ind_uni_100 (cost=0.00..314.00
rows=50000 width=0) (actual time=14.226..14.226 rows=49758 loops=1)
Index Cond: (b = 1)
Total runtime: 73.395 ms
Which is quite reasonable.The table has 1.000.000 rows (17.242 pages). From
pg_stat_get_blocks_fetched I can see that there were 102 page requests for
table. So all things seem to work great here!
But if I multiply the size of the table ten-times (10.000.000 rows - 172.414
pages) and run the same query I get:
explain analyze select * from ind_uni_1000 where a=1 and b=1 and c=1
Bitmap Heap Scan on ind_uni_1000 (cost=9369.50..14055.74 rows=1250 width=104)
(actual time=18111.415..176747.937 rows=1251 loops=1)
Recheck Cond: ((c = 1) AND (a = 1) AND (b = 1))
-> BitmapAnd (cost=9369.50..9369.50 rows=1250 width=0) (actual
time=17684.587..17684.587 rows=0 loops=1)
-> Bitmap Index Scan on index_c_ind_uni_1000 (cost=0.00..3123.00
rows=500000 width=0) (actual time=5704.624..5704.624 rows=500910 loops=1)
Index Cond: (c = 1)
-> Bitmap Index Scan on index_a_ind_uni_1000 (cost=0.00..3123.00
rows=500000 width=0) (actual time=6147.962..6147.962 rows=500080 loops=1)
Index Cond: (a = 1)
-> Bitmap Index Scan on index_b_ind_uni_1000 (cost=0.00..3123.00
rows=500000 width=0) (actual time=5767.754..5767.754 rows=500329 loops=1)
Index Cond: (b = 1)
Total runtime: 176753.200 ms
which is slower even than a seq scan. Now I get that there were 131.398 page
requests for table in order to retrieve almost 1250 tuples!Can someone explain
why this is happening? All memory parameters are set to default.
Thanks!
jkapad@csd.uoc.gr writes:
> ... is quite reasonable.The table has 1.000.000 rows (17.242 pages). From
> pg_stat_get_blocks_fetched I can see that there were 102 page requests for
> table. So all things seem to work great here!
> But if I multiply the size of the table ten-times (10.000.000 rows - 172.414
> pages) and run the same query I get:
> ...
> which is slower even than a seq scan. Now I get that there were 131.398 page
> requests for table in order to retrieve almost 1250 tuples!Can someone explain
> why this is happening? All memory parameters are set to default.
You probably need to increase work_mem so that the bitmaps don't become
lossy ...
regards, tom lane
Yes that was the problem! Thank you very much On Thu, 13 Jul 2006, Tom Lane wrote: > jkapad@csd.uoc.gr writes: >> ... is quite reasonable.The table has 1.000.000 rows (17.242 pages). From >> pg_stat_get_blocks_fetched I can see that there were 102 page requests for >> table. So all things seem to work great here! > >> But if I multiply the size of the table ten-times (10.000.000 rows - 172.414 >> pages) and run the same query I get: >> ... >> which is slower even than a seq scan. Now I get that there were 131.398 page >> requests for table in order to retrieve almost 1250 tuples!Can someone explain >> why this is happening? All memory parameters are set to default. > > You probably need to increase work_mem so that the bitmaps don't become > lossy ... > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >