equivalence class not working?
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
aqualification 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!
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
Fine. The rest documentation says:" For now, the test only uses
restrictionclauses (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...
Show quoted text
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 herewith
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
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