Thread: bitmap-index-scan slower than normal index scan

bitmap-index-scan slower than normal index scan

From
Andreas Kretschmer
Date:
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°

Re: bitmap-index-scan slower than normal index scan

From
"Alex Deucher"
Date:
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

Re: bitmap-index-scan slower than normal index scan

From
"A. Kretschmer"
Date:
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

Re: bitmap-index-scan slower than normal index scan

From
"Alex Deucher"
Date:
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

Re: bitmap-index-scan slower than normal index scan

From
"Alex Deucher"
Date:
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

Re: bitmap-index-scan slower than normal index scan

From
Tom Lane
Date:
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

Re: bitmap-index-scan slower than normal index scan

From
Andreas Kretschmer
Date:
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°

Re: bitmap-index-scan slower than normal index scan

From
"A. Kretschmer"
Date:
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