Re: Query performance problems with partitioned tables

От: Tom Lane
Тема: Re: Query performance problems with partitioned tables
Дата: ,
Msg-id: 27303.1177945607@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Query performance problems with partitioned tables  (Andreas Haumer)
Ответы: Re: Query performance problems with partitioned tables  (Andreas Haumer)
Список: pgsql-performance

Скрыть дерево обсуждения

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 по дате сообщения:

От: Gregory Stark
Дата:
Сообщение: Re: Query performance problems with partitioned tables
От: Josh Berkus
Дата:
Сообщение: Re: Feature Request --- was: PostgreSQL Performance Tuning