Re: [PERFORM] Question about Bitmap Heap Scan/BitmapAnd

2007-02-15 Thread Guillaume Smet

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


Re: [PERFORM] Question about Bitmap Heap Scan/BitmapAnd

2007-02-15 Thread Guillaume Smet

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

2007-02-15 Thread Tom Lane
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

2007-02-15 Thread Alvaro Herrera
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

2007-02-15 Thread Guillaume Smet

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

2007-02-13 Thread Tom Lane
"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


Re: [PERFORM] Question about Bitmap Heap Scan/BitmapAnd

2007-02-13 Thread Guillaume Smet

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

2007-02-13 Thread Tom Lane
"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


[PERFORM] Question about Bitmap Heap Scan/BitmapAnd

2007-02-13 Thread Guillaume Smet

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