Re: Proposed Query Planner TODO items

On 16 Feb, Tom Lane wrote:
> markw(at)osdl(dot)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.

It looks like we have indexes on all of the date columns except
l_commitdate, which appears to be in Q4.

So I think I'll run against the CVS tip as is, again with an index on
l_commitdate, and then another test to confirm your theory. Sound good?

Mark