pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuestion about Bitmap Heap Scan/BitmapAnd
9+ messages / 3 participants
[nested] [flat]
* Question about Bitmap Heap Scan/BitmapAnd
@ 2007-02-13 16:32 Guillaume Smet <guillaume.smet@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Guillaume Smet @ 2007-02-13 16:32 UTC (permalink / raw)
To: pgsql-performance
Hi all,
I'm currently working on optimizing a couple of queries. While
studying the EXPLAIN ANALYZE output of a query, I found this Bitmap
Heap Scan node:
-> Bitmap Heap Scan on lieu l (cost=12.46..63.98 rows=53 width=94)
(actual time=35.569..97.166 rows=78 loops=1)
Recheck Cond: ('(4190964.86112204, 170209.656489245,
4801644.52951672),(4194464.86111106, 173709.656478266,
4805144.52950574)'::cube @ (ll_to_earth((wgslat)::double precision,
(wgslon)::double precision))::cube)
Filter: (parking AND (numlieu <> 0))
-> BitmapAnd (cost=12.46..12.46 rows=26 width=0) (actual
time=32.902..32.902 rows=0 loops=1)
-> Bitmap Index Scan on idx_lieu_earth (cost=0.00..3.38
rows=106 width=0) (actual time=30.221..30.221 rows=5864 loops=1)
Index Cond: ('(4190964.86112204, 170209.656489245,
4801644.52951672),(4194464.86111106, 173709.656478266,
4805144.52950574)'::cube @ (ll_to_earth((wgslat)::double precision,
(wgslon)::double precision))::cube)
-> Bitmap Index Scan on idx_lieu_parking (cost=0.00..8.83
rows=26404 width=0) (actual time=0.839..0.839 rows=1095 loops=1)
Index Cond: (parking = true)
What surprises me is that "parking" is in the filter and not in the
Recheck Cond whereas it's part of the second Bitmap Index Scan of the
Bitmap And node.
AFAIK, BitmapAnd builds a bitmap of the pages returned by the two
Bitmap Index Scans so I supposed it should append both Index Cond in
the Recheck Cond.
Is there a reason why the second Index Cond in the filter? Does it
make a difference in terms of performance (I suppose no but I'd like
to have a confirmation)?
Thanks.
--
Guillaume
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Question about Bitmap Heap Scan/BitmapAnd
@ 2007-02-13 16:49 Tom Lane <tgl@sss.pgh.pa.us>
parent: Guillaume Smet <guillaume.smet@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2007-02-13 16:49 UTC (permalink / raw)
To: Guillaume Smet <guillaume.smet@gmail.com>; +Cc: pgsql-performance
"Guillaume Smet" <guillaume.smet@gmail.com> writes:
> What surprises me is that "parking" is in the filter and not in the
> Recheck Cond whereas it's part of the second Bitmap Index Scan of the
> Bitmap And node.
That's probably because of this:
/*
* When dealing with special or lossy operators, we will at this point
* have duplicate clauses in qpqual and bitmapqualorig. We may as well
* drop 'em from bitmapqualorig, since there's no point in making the
* tests twice.
*/
bitmapqualorig = list_difference_ptr(bitmapqualorig, qpqual);
What's not immediately clear is why the condition was in both lists to
start with. Perhaps idx_lieu_parking is a partial index with this as
its WHERE condition?
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Question about Bitmap Heap Scan/BitmapAnd
@ 2007-02-13 17:17 Guillaume Smet <guillaume.smet@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Guillaume Smet @ 2007-02-13 17:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On 2/13/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> bitmapqualorig = list_difference_ptr(bitmapqualorig, qpqual);
>
> What's not immediately clear is why the condition was in both lists to
> start with. Perhaps idx_lieu_parking is a partial index with this as
> its WHERE condition?
Yes, it is: "idx_lieu_parking" btree (parking) WHERE parking = true .
Sorry for not pointing it immediatly.
If not, the index is not used at all (there are very few lines in lieu
with parking=true).
So the basic explanation is that it's in both lists due to the partial
index and only qpqual keeps the condition? I would have expected the
opposite but it doesn't change anything I suppose?
Thanks for your answer.
--
Guillaume
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Question about Bitmap Heap Scan/BitmapAnd
@ 2007-02-13 17:51 Tom Lane <tgl@sss.pgh.pa.us>
parent: Guillaume Smet <guillaume.smet@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2007-02-13 17:51 UTC (permalink / raw)
To: Guillaume Smet <guillaume.smet@gmail.com>; +Cc: pgsql-performance
"Guillaume Smet" <guillaume.smet@gmail.com> writes:
> So the basic explanation is that it's in both lists due to the partial
> index and only qpqual keeps the condition? I would have expected the
> opposite but it doesn't change anything I suppose?
It gets the right answer, yes. I'm not sure if we could safely put the
condition into the recheck instead of the filter. The particular code
I showed you has to go the direction it does, because a condition in the
filter has to be checked even if the bitmap is not lossy. I seem to
recall concluding that we had to recheck partial-index conditions even
if the bitmap is not lossy, but I can't reconstruct my reasoning at the
moment.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Question about Bitmap Heap Scan/BitmapAnd
@ 2007-02-15 16:05 Guillaume Smet <guillaume.smet@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Guillaume Smet @ 2007-02-15 16:05 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Tom,
On 2/13/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> It gets the right answer, yes. I'm not sure if we could safely put the
> condition into the recheck instead of the filter. The particular code
> I showed you has to go the direction it does, because a condition in the
> filter has to be checked even if the bitmap is not lossy. I seem to
> recall concluding that we had to recheck partial-index conditions even
> if the bitmap is not lossy, but I can't reconstruct my reasoning at the
> moment.
I'm still working on my proximity query, testing PostGIS now. I
noticed an issue with a gist index on a point which seems related to
my previous question.
I have the following in my plan:
-> Bitmap Heap Scan on lieu l (cost=13.37..1555.69 rows=844
width=118) (actual time=3.672..39.497 rows=1509 loops=1)
Filter: (((dfinvalidlieu IS NULL) OR (dfinvalidlieu >= now()))
AND (wgslat IS NOT NULL) AND (wgslon IS NOT NULL) AND (wgslat <>
41.89103400) AND (wgslon <> 12.49244400) AND (earthpoint &&
'0103000020777F0000010000000500000000000040019B334100000020D1D8514100000040019B334100000040ADDE51410000006071B2334100000040ADDE51410000006071B2334100000020D1D8514100000040019B334100000020D1D85141'::geometry)
AND (numlieu <> 49187))
-> Bitmap Index Scan on idx_lieu_earthpoint (cost=0.00..13.37
rows=1249 width=0) (actual time=2.844..2.844 rows=1510 loops=1)
Index Cond: (earthpoint &&
'0103000020777F0000010000000500000000000040019B334100000020D1D8514100000040019B334100000040ADDE51410000006071B2334100000040ADDE51410000006071B2334100000020D1D8514100000040019B334100000020D1D85141'::geometry)
Is it normal I have no recheck cond and the index cond of Bitmap Index
Scan is in the filter? Is it also a consequence of the code you
pointed?
The index was created with:
create index idx_lieu_earthpoint on lieu using gist(earthpoint
gist_geometry_ops);
--
Guillaume
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Question about Bitmap Heap Scan/BitmapAnd
@ 2007-02-15 16:27 Alvaro Herrera <alvherre@commandprompt.com>
parent: Guillaume Smet <guillaume.smet@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Alvaro Herrera @ 2007-02-15 16:27 UTC (permalink / raw)
To: Guillaume Smet <guillaume.smet@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
Guillaume Smet escribió:
> I'm still working on my proximity query, testing PostGIS now. I
> noticed an issue with a gist index on a point which seems related to
> my previous question.
>
> I have the following in my plan:
> -> Bitmap Heap Scan on lieu l (cost=13.37..1555.69 rows=844
> width=118) (actual time=3.672..39.497 rows=1509 loops=1)
> Filter: (((dfinvalidlieu IS NULL) OR (dfinvalidlieu >= now()))
> AND (wgslat IS NOT NULL) AND (wgslon IS NOT NULL) AND (wgslat <>
> 41.89103400) AND (wgslon <> 12.49244400) AND (earthpoint &&
> '0103000020777F0000010000000500000000000040019B334100000020D1D8514100000040019B334100000040ADDE51410000006071B2334100000040ADDE51410000006071B2334100000020D1D8514100000040019B334100000020D1D85141'::geometry)
> AND (numlieu <> 49187))
> -> Bitmap Index Scan on idx_lieu_earthpoint (cost=0.00..13.37
> rows=1249 width=0) (actual time=2.844..2.844 rows=1510 loops=1)
> Index Cond: (earthpoint &&
> '0103000020777F0000010000000500000000000040019B334100000020D1D8514100000040019B334100000040ADDE51410000006071B2334100000040ADDE51410000006071B2334100000020D1D8514100000040019B334100000020D1D85141'::geometry)
>
> Is it normal I have no recheck cond and the index cond of Bitmap Index
> Scan is in the filter? Is it also a consequence of the code you
> pointed?
It is in the filter, is it not? Having a recheck would be redundant.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Question about Bitmap Heap Scan/BitmapAnd
@ 2007-02-15 16:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2007-02-15 16:34 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Guillaume Smet <guillaume.smet@gmail.com>; pgsql-performance
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Guillaume Smet escribió:
>> Is it normal I have no recheck cond and the index cond of Bitmap Index
>> Scan is in the filter? Is it also a consequence of the code you
>> pointed?
> It is in the filter, is it not? Having a recheck would be redundant.
Yeah, but his question is why is it in the filter? I think that the
answer is probably "because the index is lossy for this operator,
so it has to be checked even if the bitmap didn't become lossy".
You'd have to check the GIST opclass definition to be sure.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Question about Bitmap Heap Scan/BitmapAnd
@ 2007-02-15 18:09 Guillaume Smet <guillaume.smet@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Guillaume Smet @ 2007-02-15 18:09 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alvaro Herrera <alvherre@commandprompt.com>; pgsql-performance
On 2/15/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I think that the
> answer is probably "because the index is lossy for this operator,
> so it has to be checked even if the bitmap didn't become lossy".
> You'd have to check the GIST opclass definition to be sure.
Any idea on what I have to look for (if it's of any interest for
anyone, otherwise, I can live with your answer)?
Thanks.
--
Guillaume
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Question about Bitmap Heap Scan/BitmapAnd
@ 2007-02-16 00:32 Guillaume Smet <guillaume.smet@gmail.com>
parent: Guillaume Smet <guillaume.smet@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Guillaume Smet @ 2007-02-16 00:32 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alvaro Herrera <alvherre@commandprompt.com>; pgsql-performance
On 2/15/07, Guillaume Smet <guillaume.smet@gmail.com> wrote:
> On 2/15/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > I think that the
> > answer is probably "because the index is lossy for this operator,
> > so it has to be checked even if the bitmap didn't become lossy".
> > You'd have to check the GIST opclass definition to be sure.
FYI I've taken a look at PostGIS source code and the index is lossy
for the operator &&:
OPERATOR 3 && RECHECK,
(for every operator in the opclass to be exact)
--
Guillaume
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2007-02-16 00:32 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-13 16:32 Question about Bitmap Heap Scan/BitmapAnd Guillaume Smet <guillaume.smet@gmail.com>
2007-02-13 16:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-02-13 17:17 ` Guillaume Smet <guillaume.smet@gmail.com>
2007-02-13 17:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-02-15 16:05 ` Guillaume Smet <guillaume.smet@gmail.com>
2007-02-15 16:27 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-02-15 16:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-02-15 18:09 ` Guillaume Smet <guillaume.smet@gmail.com>
2007-02-16 00:32 ` Guillaume Smet <guillaume.smet@gmail.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