From: Andreas Haumer <andreas@xss.co.at>
To: Neil Peter Braggio <pbraggio@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query performance problems with partitioned tables
Date: Mon, 30 Apr 2007 18:58:15 +0200
Message-ID: <46362027.9000803@xss.co.at> (raw)
In-Reply-To: <a722ba580704300706o24947e13id5b355684a5e0ff@mail.gmail.com>
References: <4635D678.3050205@xss.co.at>
<87ps5mgghp.fsf@meuh.mnc.lan>
<4635EF3A.1070204@xss.co.at>
<4635F2E1.3090602@archonet.com>
<a722ba580704300706o24947e13id5b355684a5e0ff@mail.gmail.com>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi!
Neil Peter Braggio schrieb:
> Just cast the value in the WHERE clause:
>
> select ts from mwdb.t_mv where zr=3622 and ts > '2006-01-01 00:00:00'
> ::TIMESTAMP order by ts asc limit 1;
>
> This search only into the right partitioned tables if you build the
> rules based in the ts field.
>
This doesn't help.
A cast is not needed in this case, as the following query
shows, where the query planner already is able to reduce
the scan to the right tables:
testdb_part=> select ts from mwdb.t_mv where zr=3622 and ts > '2005-12-31 22:00:00' and ts < '2006-01-01 02:00:00';
ts
- ------------------------
2005-12-31 23:00:00+01
2006-01-01 00:00:00+01
2006-01-01 01:00:00+01
(3 rows)
testdb_part=> explain analyze select ts from mwdb.t_mv where zr=3622 and ts > '2005-12-31 22:00:00' and ts < '2006-01-01 02:00:00';
QUERY PLAN
- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.00..26.64 rows=4 width=8) (actual time=0.040..0.088 rows=3 loops=1)
-> Append (cost=0.00..26.64 rows=4 width=8) (actual time=0.035..0.071 rows=3 loops=1)
-> Index Scan using i_mv_ts on t_mv (cost=0.00..8.27 rows=1 width=8) (actual time=0.010..0.010 rows=0 loops=1)
Index Cond: (((ts)::timestamp with time zone > '2005-12-31 22:00:00+01'::timestamp with time zone) AND ((ts)::timestamp with time zone < '2006-01-01 02:00:00+01'::timestamp with time zone))
Filter: ((zr)::integer = 3622)
-> Index Scan using pk_mv_200512 on t_mv_200512 t_mv (cost=0.00..8.30 rows=1 width=8) (actual time=0.019..0.022 rows=1 loops=1)
Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone > '2005-12-31 22:00:00+01'::timestamp with time zone) AND ((ts)::timestamp with time zone < '2006-01-01 02:00:00+01'::timestamp with time zone))
-> Index Scan using pk_mv_200601 on t_mv_200601 t_mv (cost=0.00..10.07 rows=2 width=8) (actual time=0.014..0.019 rows=2 loops=1)
Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone > '2005-12-31 22:00:00+01'::timestamp with time zone) AND ((ts)::timestamp with time zone < '2006-01-01 02:00:00+01'::timestamp with time zone))
Total runtime: 0.176 ms
(10 rows)
Here, two child tables are involved (t_mv_200512 and t_mv_200601)
and the query only uses those two, even without cast of the constants
in the where clause.
- - 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
iD8DBQFGNiATxJmyeGcXPhERAo23AJwPCBwvWQT/m3QRXRWqK0aECeMQ2gCbBDjA
E5iZNnU41vrFBNtXzdCSmWY=
=0+pC
-----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, pbraggio@gmail.com
Subject: Re: Query performance problems with partitioned tables
In-Reply-To: <46362027.9000803@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