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.023rows=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