From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andreas Haumer <andreas@xss.co.at>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query performance problems with partitioned tables
Date: Mon, 30 Apr 2007 11:06:47 -0400
Message-ID: <27303.1177945607@sss.pgh.pa.us> (raw)
In-Reply-To: <4635D678.3050205@xss.co.at>
References: <4635D678.3050205@xss.co.at>
Andreas Haumer <andreas@xss.co.at> writes:
> A simple example: Get the timestamp of a measurement value for time
> series 3622 which is right before the measurement value with time
> stamp '2007-04-22 00:00:00':
> testdb_std=> select ts from mwdb.t_mv where zr=3622 and ts < '2007-04-22 00:00:00' order by ts desc limit 1;
As already pointed out, this is only going to be able to exclude
partitions that are strictly after the limit-time, since you have no
WHERE clause that excludes anything before. Can you set a reasonable
upper bound on the maximum inter-measurement time? If so, you could
query something like this:
select ts from mwdb.t_mv where zr=3622 and ts < '2007-04-22 00:00:00'
and ts > '2007-04-21 00:00:00'
order by ts desc limit 1;
If you don't have a hard limit, but do have some smarts on the client
side, you could try successive queries like this with larger and larger
windows until you get an answer.
regards, tom lane
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: tgl@sss.pgh.pa.us, andreas@xss.co.at
Subject: Re: Query performance problems with partitioned tables
In-Reply-To: <27303.1177945607@sss.pgh.pa.us>
* 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