Re: Query performance problems with partitioned tables

From: Tom Lane
Subject: Re: Query performance problems with partitioned tables
Date: ,
Msg-id: 27303.1177945607@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Query performance problems with partitioned tables  (Andreas Haumer)
Responses: Re: Query performance problems with partitioned tables  (Andreas Haumer)
List: pgsql-performance

Tree view

Query performance problems with partitioned tables  (Andreas Haumer, )
 Re: Query performance problems with partitioned tables  (Guillaume Cottenceau, )
  Re: Query performance problems with partitioned tables  (Andreas Haumer, )
   Re: Query performance problems with partitioned tables  (Richard Huxton, )
    Re: Query performance problems with partitioned tables  ("Neil Peter Braggio", )
     Re: Query performance problems with partitioned tables  (Andreas Haumer, )
   Re: Query performance problems with partitioned tables  (Guillaume Cottenceau, )
    Re: Query performance problems with partitioned tables  (Gregory Stark, )
   Re: Query performance problems with partitioned tables  ("Steinar H. Gunderson", )
 Re: Query performance problems with partitioned tables  (Tom Lane, )
  Re: Query performance problems with partitioned tables  (Andreas Haumer, )
   Re: Query performance problems with partitioned tables  (Gregory Stark, )
 Re: Query performance problems with partitioned tables  (Fei Liu, )
  Re: Query performance problems with partitioned tables  ("Merlin Moncure", )
   Re: Query performance problems with partitioned tables  (Scott Marlowe, )
    Re: Query performance problems with partitioned tables  ("Merlin Moncure", )
    Re: Query performance problems with partitioned tables  (Fei Liu, )
     Re: Query performance problems with partitioned tables  (Scott Marlowe, )

Andreas Haumer <> 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


pgsql-performance by date:

From: Gregory Stark
Date:
Subject: Re: Query performance problems with partitioned tables
From: Josh Berkus
Date:
Subject: Re: Feature Request --- was: PostgreSQL Performance Tuning