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

Re: Query performance problems with partitioned tables

From: Andreas Haumer <andreas(at)xss(dot)co(dot)at>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Query performance problems with partitioned tables
Date: 2007-04-30 17:20:49
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
Hash: SHA1


Tom Lane schrieb:
> 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;

That might be possible, though I'll have to check with our
business logic. Those "open interval" queries usually are
needed to catch the immediate neighbors for navigation
purposes (e.g. the "next" and "previous" values in a list)
or for drawing diagrams where the line starts somewhere
left or right "outside" the diagram.

> 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.

Well, the beauty of the "inheritance method" of course is
to keep such rules out of the application... ;-)

I have a DAO layer on top of Hibernate and I'd rather not
touch this to put special database access logic in (especially
as I plan to use partitioned tables as an option for really
large installations. For small ones it looks like we don't
need or want partitioned tables anyway)

Perhaps I can hide this logic in some stored procedures
(I already have several stored procedures to handle
automatic and transparent creation of child tables on
INSERTs anyway...)

- - andreas

- --
Andreas Haumer                     | mailto:andreas(at)xss(dot)co(dot)at
*x Software + Systeme              |
Karmarschgasse 51/2/20             | Tel: +43-1-6060114-0
A-1100 Vienna, Austria             | Fax: +43-1-6060114-71
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla -


In response to


pgsql-performance by date

Next:From: Steinar H. GundersonDate: 2007-04-30 17:23:34
Subject: Re: Query performance problems with partitioned tables
Previous:From: Andreas HaumerDate: 2007-04-30 16:58:15
Subject: Re: Query performance problems with partitioned tables

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