To: pgsql-performance@postgresql.org
From: =?ISO-8859-1?Q?Nis_J=F8rgensen?= <nis@superlativ.dk>
Subject: Re: 500rows = 1min/2.5k rows=20min/6K rows 2 hours and still running
Date: Wed, 12 Sep 2007 15:14:25 +0200
Lines: 34
Message-ID: <fc8oo2$74o$1@sea.gmane.org>
References: <1189529868.17184.17.camel@neuromancer.home.net>	
	<1189530143.17184.21.camel@neuromancer.home.net>	
	<15496.1189535000@sss.pgh.pa.us>	
	<1189563332.32450.11.camel@neuromancer.home.net>
	<1189584573.13796.12.camel@neuromancer.home.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
User-Agent: Thunderbird 2.0.0.6 (Windows/20070728)
In-Reply-To: <1189584573.13796.12.camel@neuromancer.home.net>

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