markw@osdl.org writes:
> I ran a test with the CAST you recommended for Q4 over the weekend:
> http://developer.osdl.org/markw/dbt3-pgsql/68/
> But it didn't seem to have much of an affect on Q4, compared to run
> #66. I'll still give the CVS tip a try.
Hm. Disappointing. I can see from the EXPLAIN results that it is
picking up the additional index constraint correctly in this run.
That should have saved a good number of useless heap fetches.
[ works with the numbers a little... ] Actually, I guess it did:
it looks like the time spent in the indexscan proper went down from
44msec to 7msec. The problem is that the bulk of the query time is
actually going into the repeated EXISTS() sub-selects, and those didn't
get any better.
There are some other queries in the set that also have date limits of
this kind, so I still think it's worth redoing a run with CVS tip to
see if we pick up anything overall. (You do have indexes created on
all the date columns no?)
There's probably no way to make Q4 fly without finding a way to optimize
the EXISTS into an IN-join. I'll put that on my to-do list ... in the
meantime, if you feel like making a run to confirm that theory, try
modifying Q4 to replace
and exists ( select * from lineitem where l_orderkey = o_orderkey and l_commitdate < l_receiptdate )
with
and o_orderkey in ( select l_orderkey from lineitem where l_commitdate < l_receiptdate )
I think that either 7.4 or CVS tip will do better with this variant,
but it probably ought to be checked.
regards, tom lane
