pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedbitmap-index-scan slower than normal index scan
8+ messages / 4 participants
[nested] [flat]
* bitmap-index-scan slower than normal index scan
@ 2007-07-11 18:36 Andreas Kretschmer <akretschmer@spamfence.net>
2007-07-11 18:52 ` Re: bitmap-index-scan slower than normal index scan Alex Deucher <alexdeucher@gmail.com>
2007-07-11 20:04 ` Re: bitmap-index-scan slower than normal index scan Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 8+ messages in thread
From: Andreas Kretschmer @ 2007-07-11 18:36 UTC (permalink / raw)
To: pgsql-performance
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.459 rows=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°
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: bitmap-index-scan slower than normal index scan
2007-07-11 18:36 bitmap-index-scan slower than normal index scan Andreas Kretschmer <akretschmer@spamfence.net>
@ 2007-07-11 18:52 ` Alex Deucher <alexdeucher@gmail.com>
2007-07-11 19:31 ` Re: bitmap-index-scan slower than normal index scan A. Kretschmer <andreas.kretschmer@schollglas.com>
1 sibling, 1 reply; 8+ messages in thread
From: Alex Deucher @ 2007-07-11 18:52 UTC (permalink / raw)
To: Andreas Kretschmer <akretschmer@spamfence.net>; +Cc: pgsql-performance
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=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.459 rows=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...
>
your results are getting cached. try two queries in a row with the same plan.
Alex
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: bitmap-index-scan slower than normal index scan
2007-07-11 18:36 bitmap-index-scan slower than normal index scan Andreas Kretschmer <akretschmer@spamfence.net>
2007-07-11 18:52 ` Re: bitmap-index-scan slower than normal index scan Alex Deucher <alexdeucher@gmail.com>
@ 2007-07-11 19:31 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-07-11 20:01 ` Re: bitmap-index-scan slower than normal index scan Alex Deucher <alexdeucher@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: A. Kretschmer @ 2007-07-11 19:31 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: bitmap-index-scan slower than normal index scan
2007-07-11 18:36 bitmap-index-scan slower than normal index scan Andreas Kretschmer <akretschmer@spamfence.net>
2007-07-11 18:52 ` Re: bitmap-index-scan slower than normal index scan Alex Deucher <alexdeucher@gmail.com>
2007-07-11 19:31 ` Re: bitmap-index-scan slower than normal index scan A. Kretschmer <andreas.kretschmer@schollglas.com>
@ 2007-07-11 20:01 ` Alex Deucher <alexdeucher@gmail.com>
2007-07-11 20:01 ` Re: bitmap-index-scan slower than normal index scan Alex Deucher <alexdeucher@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Alex Deucher @ 2007-07-11 20:01 UTC (permalink / raw)
To: A. Kretschmer <andreas.kretschmer@schollglas.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: bitmap-index-scan slower than normal index scan
2007-07-11 18:36 bitmap-index-scan slower than normal index scan Andreas Kretschmer <akretschmer@spamfence.net>
2007-07-11 18:52 ` Re: bitmap-index-scan slower than normal index scan Alex Deucher <alexdeucher@gmail.com>
2007-07-11 19:31 ` Re: bitmap-index-scan slower than normal index scan A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-07-11 20:01 ` Re: bitmap-index-scan slower than normal index scan Alex Deucher <alexdeucher@gmail.com>
@ 2007-07-11 20:01 ` Alex Deucher <alexdeucher@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Alex Deucher @ 2007-07-11 20:01 UTC (permalink / raw)
To: A. Kretschmer <andreas.kretschmer@schollglas.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: bitmap-index-scan slower than normal index scan
2007-07-11 18:36 bitmap-index-scan slower than normal index scan Andreas Kretschmer <akretschmer@spamfence.net>
@ 2007-07-11 20:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-07-11 20:19 ` Re: bitmap-index-scan slower than normal index scan Andreas Kretschmer <akretschmer@spamfence.net>
1 sibling, 1 reply; 8+ messages in thread
From: Tom Lane @ 2007-07-11 20:04 UTC (permalink / raw)
To: Andreas Kretschmer <akretschmer@spamfence.net>; +Cc: pgsql-performance
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.459 rows=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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: bitmap-index-scan slower than normal index scan
2007-07-11 18:36 bitmap-index-scan slower than normal index scan Andreas Kretschmer <akretschmer@spamfence.net>
2007-07-11 20:04 ` Re: bitmap-index-scan slower than normal index scan Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-07-11 20:19 ` Andreas Kretschmer <akretschmer@spamfence.net>
2007-07-12 05:06 ` Re: bitmap-index-scan slower than normal index scan A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 1 reply; 8+ messages in thread
From: Andreas Kretschmer @ 2007-07-11 20:19 UTC (permalink / raw)
To: pgsql-performance
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°
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: bitmap-index-scan slower than normal index scan
2007-07-11 18:36 bitmap-index-scan slower than normal index scan Andreas Kretschmer <akretschmer@spamfence.net>
2007-07-11 20:04 ` Re: bitmap-index-scan slower than normal index scan Tom Lane <tgl@sss.pgh.pa.us>
2007-07-11 20:19 ` Re: bitmap-index-scan slower than normal index scan Andreas Kretschmer <akretschmer@spamfence.net>
@ 2007-07-12 05:06 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 0 replies; 8+ messages in thread
From: A. Kretschmer @ 2007-07-12 05:06 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2007-07-12 05:06 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-11 18:36 bitmap-index-scan slower than normal index scan Andreas Kretschmer <akretschmer@spamfence.net>
2007-07-11 18:52 ` Alex Deucher <alexdeucher@gmail.com>
2007-07-11 19:31 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-07-11 20:01 ` Alex Deucher <alexdeucher@gmail.com>
2007-07-11 20:01 ` Alex Deucher <alexdeucher@gmail.com>
2007-07-11 20:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-07-11 20:19 ` Andreas Kretschmer <akretschmer@spamfence.net>
2007-07-12 05:06 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox