Re: TPCH 100GB - need some help

Mr. Tom Lane

--- Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Eduardo Almeida <edalmeida(at)yahoo(dot)com> writes:
> > I need some help in a TPCH 100GB benchmark.
> > Here I put the query #19, the explain and the
> "top"
> > for it.
>
> IIRC, this is one of the cases that inspired the
> work that's been done
> on the query optimizer for 7.5. I don't think you
> will be able to get
> 7.4 to generate a good plan for it (at least not
> without changing the
> query, which is against the TPC rules). How do you
> feel about running
> CVS tip?

We are testing the postgre 7.4.2 to show results to
some projects here in Brazil. We are near the deadline
for these projects and we need to show results with a
stable version.

ASAP I want and I will help the PG community testing
the CVS with VLDB.

>
> BTW, are you aware that OSDL has already done a good
> deal of work with
> running TPC benchmarks for Postgres (and some other
> OS databases)?

No! Now I'm considering the use of OSDL because of
query rewrite. Yesterday the query #19 that I describe
runs in the OSDL way.

We found some interesting patterns in queries that
take to long to finish in the 100 GB test.
Sub-queries inside other sub-queries (Q20 and Q22);
Exists and Not exists selection (Q4, Q21 and Q22);
Aggregations with in-line views, that is queries
inside FROM clause (Q7, Q8, Q9 and Q22);

In fact these queries were aborted by timeout
statement_timeout = 25000000

I took off the timeout to Q20 and it finished in
23:53:49 hs.

tks a lot,
Eduardo

ps. sorry about my english

>
> regards, tom lane

__________________________________
Do you Yahoo!?
SBC Yahoo! - Internet access at a great low price.
http://promo.yahoo.com/sbc/