Skip site navigation (1) Skip section navigation (2)

Re: Query performance problems with partitioned tables

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Andreas Haumer <andreas(at)xss(dot)co(dot)at>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Query performance problems with partitioned tables
Date: 2007-04-30 15:06:47
Message-ID: 27303.1177945607@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Andreas Haumer <andreas(at)xss(dot)co(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Gregory StarkDate: 2007-04-30 15:35:55
Subject: Re: Query performance problems with partitioned tables
Previous:From: Fei LiuDate: 2007-04-30 14:13:46
Subject: sytem log audit/reporting and psql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group