To: Andreas Haumer <andreas@xss.co.at>
cc: pgsql-performance@postgresql.org
Subject: Re: Query performance problems with partitioned tables 
In-reply-to: <4635D678.3050205@xss.co.at> 
References: <4635D678.3050205@xss.co.at>
Comments: In-reply-to Andreas Haumer <andreas@xss.co.at>
	message dated "Mon, 30 Apr 2007 13:43:52 +0200"
Date: Mon, 30 Apr 2007 11:06:47 -0400
Message-ID: <27303.1177945607@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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