Re: [PERFORM] Question about Bitmap Heap Scan/BitmapAnd
Tom, On 2/13/07, Tom Lane [EMAIL PROTECTED] 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 '010320777F01000540019B33410020D1D851410040019B33410040ADDE5141006071B233410040ADDE5141006071B233410020D1D851410040019B33410020D1D85141'::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 '010320777F01000540019B33410020D1D851410040019B33410040ADDE5141006071B233410040ADDE5141006071B233410020D1D851410040019B33410020D1D85141'::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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Question about Bitmap Heap Scan/BitmapAnd
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 '010320777F01000540019B33410020D1D851410040019B33410040ADDE5141006071B233410040ADDE5141006071B233410020D1D851410040019B33410020D1D85141'::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 '010320777F01000540019B33410020D1D851410040019B33410040ADDE5141006071B233410040ADDE5141006071B233410020D1D851410040019B33410020D1D85141'::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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Question about Bitmap Heap Scan/BitmapAnd
Alvaro Herrera [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Question about Bitmap Heap Scan/BitmapAnd
On 2/15/07, Tom Lane [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Question about Bitmap Heap Scan/BitmapAnd
On 2/15/07, Guillaume Smet [EMAIL PROTECTED] wrote: On 2/15/07, Tom Lane [EMAIL PROTECTED] 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 : OPERATOR3 RECHECK, (for every operator in the opclass to be exact) -- Guillaume ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[PERFORM] 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.8606, 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.8606, 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
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [PERFORM] Question about Bitmap Heap Scan/BitmapAnd
Guillaume Smet [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Question about Bitmap Heap Scan/BitmapAnd
On 2/13/07, Tom Lane [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Question about Bitmap Heap Scan/BitmapAnd
Guillaume Smet [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
