Обсуждение: bitmap-index-scan slower than normal index scan
Hi,
Okay, i know, not really a recent version:
PostgreSQL 8.1.4 on i386-pc-linux-gnu, compiled by GCC cc (GCC) 3.3.5 (Debian 1:3.3.5-13)
I have a fresh ANALYZED table with some indexes.
scholl=*# set enable_bitmapscan=1;
SET
scholl=*# explain analyse select sum(flaeche) from bde_meldungen where maschine=1200 and ab = 347735;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1371.95..1371.96 rows=1 width=8) (actual time=163.788..163.790 rows=1 loops=1)
-> Bitmap Heap Scan on bde_meldungen (cost=1217.69..1371.85 rows=39 width=8) (actual time=163.702..163.758 rows=2
loops=1)
Recheck Cond: ((ab = 347735) AND (maschine = 1200))
-> BitmapAnd (cost=1217.69..1217.69 rows=39 width=0) (actual time=163.681..163.681 rows=0 loops=1)
-> Bitmap Index Scan on idx_ab (cost=0.00..5.95 rows=558 width=0) (actual time=0.078..0.078 rows=109
loops=1)
Index Cond: (ab = 347735)
-> Bitmap Index Scan on idx_maschine (cost=0.00..1211.49 rows=148997 width=0) (actual
time=163.459..163.459rows=164760 loops=1)
Index Cond: (maschine = 1200)
Total runtime: 163.901 ms
(9 rows)
Okay, 163.901 ms with Bitmap Index Scan.
And now i disable this and runs the same select:
scholl=*# set enable_bitmapscan=0;
SET
scholl=*# explain analyse select sum(flaeche) from bde_meldungen where maschine=1200 and ab = 347735;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=2142.77..2142.78 rows=1 width=8) (actual time=0.229..0.231 rows=1 loops=1)
-> Index Scan using idx_ab on bde_meldungen (cost=0.00..2142.67 rows=39 width=8) (actual time=0.046..0.209 rows=2
loops=1)
Index Cond: (ab = 347735)
Filter: (maschine = 1200)
Total runtime: 0.326 ms
(5 rows)
Okay, i got a really different plan, but i expected _NOT_ a
performance-boost like this. I expected the opposite.
It's not a really problem, i just played with this. But i'm confused
about this...
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
On 7/11/07, Andreas Kretschmer <akretschmer@spamfence.net> wrote: > Hi, > > Okay, i know, not really a recent version: > PostgreSQL 8.1.4 on i386-pc-linux-gnu, compiled by GCC cc (GCC) 3.3.5 (Debian 1:3.3.5-13) > > I have a fresh ANALYZED table with some indexes. > > scholl=*# set enable_bitmapscan=1; > SET > scholl=*# explain analyse select sum(flaeche) from bde_meldungen where maschine=1200 and ab = 347735; > QUERY PLAN > -------------------------------------------------------------------------------------------------------------------------------------------------- > Aggregate (cost=1371.95..1371.96 rows=1 width=8) (actual time=163.788..163.790 rows=1 loops=1) > -> Bitmap Heap Scan on bde_meldungen (cost=1217.69..1371.85 rows=39 width=8) (actual time=163.702..163.758 rows=2loops=1) > Recheck Cond: ((ab = 347735) AND (maschine = 1200)) > -> BitmapAnd (cost=1217.69..1217.69 rows=39 width=0) (actual time=163.681..163.681 rows=0 loops=1) > -> Bitmap Index Scan on idx_ab (cost=0.00..5.95 rows=558 width=0) (actual time=0.078..0.078 rows=109 loops=1) > Index Cond: (ab = 347735) > -> Bitmap Index Scan on idx_maschine (cost=0.00..1211.49 rows=148997 width=0) (actual time=163.459..163.459rows=164760 loops=1) > Index Cond: (maschine = 1200) > Total runtime: 163.901 ms > (9 rows) > > > Okay, 163.901 ms with Bitmap Index Scan. > > And now i disable this and runs the same select: > > scholl=*# set enable_bitmapscan=0; > SET > scholl=*# explain analyse select sum(flaeche) from bde_meldungen where maschine=1200 and ab = 347735; > QUERY PLAN > -------------------------------------------------------------------------------------------------------------------------------- > Aggregate (cost=2142.77..2142.78 rows=1 width=8) (actual time=0.229..0.231 rows=1 loops=1) > -> Index Scan using idx_ab on bde_meldungen (cost=0.00..2142.67 rows=39 width=8) (actual time=0.046..0.209 rows=2loops=1) > Index Cond: (ab = 347735) > Filter: (maschine = 1200) > Total runtime: 0.326 ms > (5 rows) > > Okay, i got a really different plan, but i expected _NOT_ a > performance-boost like this. I expected the opposite. > > > It's not a really problem, i just played with this. But i'm confused > about this... > your results are getting cached. try two queries in a row with the same plan. Alex
am Wed, dem 11.07.2007, um 14:52:01 -0400 mailte Alex Deucher folgendes: > >Okay, i got a really different plan, but i expected _NOT_ a > >performance-boost like this. I expected the opposite. > > > > > >It's not a really problem, i just played with this. But i'm confused > >about this... > > > > your results are getting cached. try two queries in a row with the same > plan. Thanks for the response, but I've done this, no difference. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
On 7/11/07, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote: > am Wed, dem 11.07.2007, um 14:52:01 -0400 mailte Alex Deucher folgendes: > > >Okay, i got a really different plan, but i expected _NOT_ a > > >performance-boost like this. I expected the opposite. > > > > > > > > >It's not a really problem, i just played with this. But i'm confused > > >about this... > > > > > > > your results are getting cached. try two queries in a row with the same > > plan. > > Thanks for the response, but I've done this, no difference. > try bumping up the default stats target on the table in question and see if that helps the planner choose a better plan. Alex
On 7/11/07, Alex Deucher <alexdeucher@gmail.com> wrote: > On 7/11/07, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote: > > am Wed, dem 11.07.2007, um 14:52:01 -0400 mailte Alex Deucher folgendes: > > > >Okay, i got a really different plan, but i expected _NOT_ a > > > >performance-boost like this. I expected the opposite. > > > > > > > > > > > >It's not a really problem, i just played with this. But i'm confused > > > >about this... > > > > > > > > > > your results are getting cached. try two queries in a row with the same > > > plan. > > > > Thanks for the response, but I've done this, no difference. > > > > try bumping up the default stats target on the table in question and > see if that helps the planner choose a better plan. > and be sure to run analyze again. Alex
Andreas Kretschmer <akretschmer@spamfence.net> writes:
> Okay, i know, not really a recent version:
> PostgreSQL 8.1.4 on i386-pc-linux-gnu, compiled by GCC cc (GCC) 3.3.5 (Debian 1:3.3.5-13)
You need a newer one.
> -> BitmapAnd (cost=1217.69..1217.69 rows=39 width=0) (actual time=163.681..163.681 rows=0 loops=1)
> -> Bitmap Index Scan on idx_ab (cost=0.00..5.95 rows=558 width=0) (actual time=0.078..0.078 rows=109
loops=1)
> Index Cond: (ab = 347735)
> -> Bitmap Index Scan on idx_maschine (cost=0.00..1211.49 rows=148997 width=0) (actual
time=163.459..163.459rows=164760 loops=1)
> Index Cond: (maschine = 1200)
This is simply a stupid choice on the part of choose_bitmap_and() ---
it's adding on a second index to try to filter on maschine when that
scan will actually just increase the cost.
I've revisited choose_bitmap_and() a couple times since then; try
8.1.9 and see if it gets this right.
Also, part of the problem here looks to be an overestimate of the number
of rows matching ab = 347735. It might help to increase the statistics
target for that column.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> schrieb: Thanks you and Alex for the response. > > PostgreSQL 8.1.4 on i386-pc-linux-gnu, compiled by GCC cc (GCC) 3.3.5 (Debian 1:3.3.5-13) > > You need a newer one. I know ;-) > > This is simply a stupid choice on the part of choose_bitmap_and() --- > it's adding on a second index to try to filter on maschine when that > scan will actually just increase the cost. > > I've revisited choose_bitmap_and() a couple times since then; try > 8.1.9 and see if it gets this right. Okay, but later. > > Also, part of the problem here looks to be an overestimate of the number > of rows matching ab = 347735. It might help to increase the statistics > target for that column. I will try this tomorrow and inform you about the result. I've never done this before, i need to read the docs about this. Thank you again. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
am Wed, dem 11.07.2007, um 22:19:58 +0200 mailte Andreas Kretschmer folgendes: > > Also, part of the problem here looks to be an overestimate of the number > > of rows matching ab = 347735. It might help to increase the statistics > > target for that column. > > I will try this tomorrow and inform you about the result. I've never > done this before, i need to read the docs about this. Okay, done, setting to 100 (thanks to mastermind) and now i got an Index Scan using idx_ab with a total runtime: 0.330 ms. Great, thanks. And yes, i will update soon... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net