Re: BETWEEN optimizer problems with single-value - Mailing list pgsql-performance

From Simon Riggs
Subject Re: BETWEEN optimizer problems with single-value
Date
Msg-id 1142537298.3859.497.camel@localhost.localdomain
Whole thread Raw
In response to Re: BETWEEN optimizer problems with single-value  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: BETWEEN optimizer problems with single-value
Re: BETWEEN optimizer problems with single-value
List pgsql-performance
On Thu, 2006-03-16 at 10:57 -0500, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > Trying to get the information in the wrong place would be very
> > expensive, I agree. But preparing that information when we have access
> > to it and passing it through the plan would be much cheaper.
>
> Where would that be?
>
> > The operator and the opclass are only connected via an index access
> > method, but for a particular index each column has only one opclass.
>
> If you're proposing making clauselist_selectivity depend on what indexes
> exist, I think that's very much the wrong approach.

Using available information sounds OK to me. Guess you're thinking of
the lack of plan invalidation?

>  In the first place,
> it still has to give usable answers for unindexed columns, and in the
> second place there might be multiple indexes with different opclasses
> for the same column, so the ambiguity problem still exists.

I was thinking that we would fill out the OpExpr with different
opclasses for each plan, so each one sees a different story. (I was
thinking there was a clauselist for each plan; if not, there could be.)
So the multiple index problem shouldn't exist.

Non-indexed cases still cause the problem, true.

> I have been wondering if we shouldn't add some more indexes on pg_amop
> or something to make it easier to do this sort of lookup --- we
> definitely seem to be finding multiple reasons to want to look up
> which opclasses contain a given operator.

Agreed, but still looking for better way than that.

[BTW how do you add new indexes to system tables? I want to add one to
pg_inherits but not sure where to look.]

Best Regards, Simon Riggs


pgsql-performance by date:

Previous
From: "Joshua D. Drake"
Date:
Subject: Re: 1 TB of memory
Next
From: Alvaro Herrera
Date:
Subject: Re: BETWEEN optimizer problems with single-value