Обсуждение: Question about Bitmap Heap Scan/BitmapAnd
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
"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
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
"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
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
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.
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
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
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