Re: Query performance problems with partitioned tables

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi!

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 | http://www.xss.co.at/
Karmarschgasse 51/2/20 | Tel: +43-1-6060114-0
A-1100 Vienna, Austria | Fax: +43-1-6060114-71
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD4DBQFGNiVvxJmyeGcXPhERAkbzAJj7HBK6tMZpb0RPD7iN6vpyc1tiAKC2heFx
7pnq02iqW2QosLd93Y03PA==
=pJ7q
-----END PGP SIGNATURE-----