Postgres Pro
Downloads
Home   >   mailing lists

Thread: equivalence class not working?

equivalence class not working?

From
uwcssa
Date:
not sure if this is the right place to post...
 
I am using postgres 8.1.  In indxpath.c, it says " Note: if Postgres tried to optimize queries by forming equivalence
classes over equi-joined attributes (i.e., if it recognized that a qualification such as "where a.b=c.d and a.b=5" could make use of
 an index on c.d), then we could use that equivalence class info here with joininfo_list to do more complete tests for the usability
of a partial index.  .....  XXX as of 7.1, equivalence class info *is* available."

 
 
Now i have the following two queries on TPC-H, where there is an index built on "o_totalprice".
 
explain select * from lineitem, orders where o_totalprice=l_extendedprice and l_extendedprice<2000;
explain select * from lineitem, orders where o_totalprice=l_extendedprice and l_extendedprice<2000 and o_totalprice<2000;
 
The second query uses the index while the first does not. It seems to me that both queries are the same (the "o_totalprice<2000" in the second query can be inferred).    Is there something that needs to be tuned or ...?
 
thanks a lot!

Re: equivalence class not working?

From
Tom Lane
Date:
uwcssa <uwcssa@gmail.com> writes:
> I am using postgres 8.1.  In indxpath.c, it says " Note: if Postgres tried
> to optimize queries by forming equivalence
> classes over equi-joined attributes (i.e., if it recognized that
> aqualification such as "where
> a.b=3Dc.d and a.b=3D5" could make use of
>  an index on c.d), then we could use that equivalence class info here with
> joininfo_list to do more complete tests for the usability
> of a partial index.  .....  XXX as of 7.1, equivalence class info *is*
> available."

Are you deliberately ignoring the rest of the comment?
        regards, tom lane

Re: equivalence class not working?

From
uwcssa
Date:
Fine. The rest documentation says:" For now, the test only uses restriction clauses (those in restrictinfo_list). --Nels, Dec '92", however, I understand it as being overridden by the
followup, which is:"XXX as of 7.1, equivalence class info *is* available.  Consider
improving this code as foreseen by Nels."

 

Therefore, equivalence class should be detected and used for index selection...  or anyone
could tell me if after 7.1  Postgresql has determined not to use equi-join for index selection...




 
On 1/16/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
uwcssa <uwcssa@gmail.com> writes:
> I am using postgres 8.1.  In indxpath.c, it says " Note: if Postgres tried
> to optimize queries by forming equivalence
> classes over equi-joined attributes (i.e., if it recognized that
> aqualification such as "where
> a.b=3Dc.d and a.b=3D5" could make use of
>  an index on c.d), then we could use that equivalence class info here with
> joininfo_list to do more complete tests for the usability
> of a partial index.  .....  XXX as of 7.1, equivalence class info *is*
> available."

Are you deliberately ignoring the rest of the comment?

                       regards, tom lane

Re: equivalence class not working?

From
Simon Riggs
Date:
On Mon, 2006-01-16 at 19:03 -0500, uwcssa wrote:
> Fine. The rest documentation says:" For now, the test only uses
> restriction clauses (those in restrictinfo_list). --Nels, Dec '92",
> however, I understand it as being overridden by the
> followup, which is:"XXX as of 7.1, equivalence class info *is*
> available.  Consider
> improving this code as foreseen by Nels."

All readers are invited to solve the problem.

Currently we add only implied equality conditions, so enhancing the
optimizer to cope with inequalities seems possible.

Best Regards, Simon Riggs