Re: Proposed Query Planner TODO items

On 16 Feb, Tom Lane wrote:
> 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.

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