markw@osdl.org writes:
> http://developer.osdl.org/markw/dbt3-pgsql/66/

> There's a run with a modified Q21.  Made a huge improvement in Q21.

Okay, looks like we know what we need to attack to solve Q21... actually
solving it will be a tad harder ;-) but we understand where the problem is.

I see what is going on to make Q4 slow, too.  It's this:

  where o_orderdate >= date '1995-04-01' and o_orderdate < date '1995-04-01' + interval '3 month'

(o_orderdate is of type "date", unsurprisingly).  This produces

         ->  Index Scan using i_o_orderdate on orders  (cost=0.00..2603496.38 rows=253677 width=19) (actual time=45.908..202483.023 rows=104083 loops=1)
               Index Cond: (o_orderdate >= '1995-04-01'::date)
               Filter: (((o_orderdate)::timestamp without time zone < '1995-07-01 00:00:00'::timestamp without time zone) AND (subplan))

that is, the lower bound is recognized as an indexscan constraint,
but the upper bound isn't because of the datatype mismatch.  So we end
up fetching the whole table up through its ending date.

Up to now, all we could do about this sort of issue was to suggest that
people cast to eliminate the datatype mismatch:

  where o_orderdate >= date '1995-04-01' and o_orderdate < CAST(date '1995-04-01' + interval '3 month' AS date)

but I dunno whether that's an allowed query modification under the TPC-H
rules.

As of CVS tip the issue could be eliminated by introducing
cross-data-type comparison operators between types date and timestamp
without time zone, and then making these be members of the date index
opclass.  I'm strongly tempted to do so ...

			regards, tom lane

Fri, 13 Feb 2004 19:04:00 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Proposed Query Planner TODO items 
I wrote:
> I see what is going on to make Q4 slow, too.  It's this:
>   where o_orderdate >= date '1995-04-01' and o_orderdate < date '1995-04-01' + interval '3 month'
> ...
> As of CVS tip the issue could be eliminated by introducing
> cross-data-type comparison operators between types date and timestamp
> without time zone, and then making these be members of the date index
> opclass.  I'm strongly tempted to do so ...

I have now done this, so if you care to re-sync with CVS tip you should
find that the queries using this sort of date constraint go faster.
(You do have indexes on all the date columns, no?)

			regards, tom lane

Sun, 15 Feb 2004 12:22:23 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Proposed Query Planner TODO items 
On 15 Feb, Tom Lane wrote:
> I wrote:
>> I see what is going on to make Q4 slow, too.  It's this:
>>   where o_orderdate >= date '1995-04-01' and o_orderdate < date '1995-04-01' + interval '3 month'
>> ...
>> As of CVS tip the issue could be eliminated by introducing
>> cross-data-type comparison operators between types date and timestamp
>> without time zone, and then making these be members of the date index
>> opclass.  I'm strongly tempted to do so ...
> 
> I have now done this, so if you care to re-sync with CVS tip you should
> find that the queries using this sort of date constraint go faster.
> (You do have indexes on all the date columns, no?)

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.

Mark

Mon, 16 Feb 2004 09:18:11 -0800 (PST)
markw@osdl.org
Re: Proposed Query Planner TODO items 
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

Mon, 16 Feb 2004 12:58:00 -0500
Tom Lane <tgl@sss.pgh.pa.us>
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

Mon, 16 Feb 2004 10:56:42 -0800 (PST)
markw@osdl.org
Re: Proposed Query Planner TODO items 
markw@osdl.org writes:
> 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?

Sure, it's only cycles ;-).  I am not certain that an index on
commitdate would help any, but it's worth trying.

			regards, tom lane

Mon, 16 Feb 2004 14:23:42 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Proposed Query Planner TODO items 
On 16 Feb, Tom Lane wrote:
> markw@osdl.org writes:
>> 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?
> 
> Sure, it's only cycles ;-).  I am not certain that an index on
> commitdate would help any, but it's worth trying.

http://developer.osdl.org/markw/dbt3-pgsql/70/

Those are results from a pull from CVS I did this morning.
I reverted Q4 (removed the CAST), but the extra WHERE constraints are
still in Q21.

Mark

Mon, 16 Feb 2004 15:41:07 -0800 (PST)
markw@osdl.org
Re: Proposed Query Planner TODO items 