Re: Execution plans for tpc-h

From: "Victor Muntes Mutero" <vmuntes(at)ac(dot)upc(dot)es>

> We have Postgres 7.0.2 .
>
> There is a query in TPC-H Benchmark that produces this execution plan:
>
> Aggregate (cost=698221486855.00..698221486855.00 rows=1 width=72)
> -> Nested Loop (cost=0.00..698221486855.00 rows=1 width=72)
> -> Seq Scan on part (cost=0.00..6855.00 rows=200000 width=32)
> -> Seq Scan on lineitem (cost=0.00..190439.15 rows=6001215
> width=40)

I've got to say that's the largest cost estimate I've ever seen (should
there be some sort of award?)

Look at the "rows=" values - PG thinks it's got to check zillions, so it's
obviously missed the p_partkey=l_partkey.

Assuming indexes etc are OK, try moving this out of the brackets:

...
where (
p_partkey = l_partkey and
( p_brand='Brand#12'
...
)
or ( p_brand='Brand#23'
...

Alternatively, try and explicit join and see if PG gets the message.

- Richard Huxton