Re: 500rows = 1min/2.5k rows=20min/6K rows 2 hours and still running

El-Lotso skrev:

> I'm on the verge of giving up... the schema seems simple and yet there's
> so much issues with it. Perhaps it's the layout of the data, I don't
> know. But based on the ordering/normalisation of the data and the one to
> many relationship of some tables, this is giving the planner a headache
> (and me a bulge on the head from knockin it against the wall)

I think you should look more at the db design, and less on rewriting the
query. Here are some observations:

- Your table structure is quite hard to understand (possibly because you
have changed the names) - if you want help on improving it, you will
need to explain the data to us, and possibly post some sample data.
- You seem to be lacking constraints on the tables. My guess is that
(id,ttype,start_timestamp) is unique in both trh and ts - but I cannot
tell (and neither can the query planner). Foreign key constraints might
help as well. These would also help others to understand your data, and
suggest reformulations of your queries.
- Another guess is that the ttype sets (177,197,705,742,758,766),
(69,178,198,704,757,741,765) are actually indicating some other property
a common "type" of record, and that only one of each will be present for
an id,start_timestamp combination. This may be related to the repeating
fields issue - if a certain ttype indicates that we are interested in a
certain pber_x field (and possibly that the others are empty).
- You have what looks like repeating fields - pber_x, fval_x, index_x -
in your tables. Fixing this might not improve your query, but might be a
good idea for other reasons.
- seq_date and seq_time seems like they may be redundant - are they
different casts of the same data?

All speculation. Hope it helps

Nis