From: Andreas Haumer <andreas@xss.co.at>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query performance problems with partitioned tables
Date: Mon, 30 Apr 2007 19:20:49 +0200
Message-ID: <46362571.20301@xss.co.at> (raw)
In-Reply-To: <27303.1177945607@sss.pgh.pa.us>
References: <4635D678.3050205@xss.co.at>
<27303.1177945607@sss.pgh.pa.us>
-----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@xss.co.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-----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andreas@xss.co.at, tgl@sss.pgh.pa.us
Subject: Re: Query performance problems with partitioned tables
In-Reply-To: <46362571.20301@xss.co.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox