From: Nis Jørgensen <nis@superlativ.dk>
To: pgsql-performance@postgresql.org
Subject: Re: 500rows = 1min/2.5k rows=20min/6K rows 2 hours and still running
Date: Wed, 12 Sep 2007 15:14:25 +0200
Message-ID: <fc8oo2$74o$1@sea.gmane.org> (raw)
In-Reply-To: <1189584573.13796.12.camel@neuromancer.home.net>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: nis@superlativ.dk
Subject: Re: 500rows = 1min/2.5k rows=20min/6K rows 2 hours and still running
In-Reply-To: <fc8oo2$74o$1@sea.gmane.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox