Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 701209FB283
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 30 Apr 2007 10:05:23 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 08599-02-2
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 30 Apr 2007 10:05:13 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159])
	by postgresql.org (Postfix) with ESMTP id B40F79FB3A1
	for <pgsql-performance@postgresql.org>;
	Mon, 30 Apr 2007 10:05:18 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3
Received: from mail.mnc.ch (62-2-77-205.static.cablecom.ch [62.2.77.205])
	by svr4.postgresql.org (Postfix) with ESMTP id 23E4F5AF885
	for <pgsql-performance@postgresql.org>;
	Mon, 30 Apr 2007 13:05:11 +0000 (GMT)
Received: from localhost (localhost [127.0.0.1])
	by mail.mnc.ch (Postfix) with ESMTP id 5B1B0A82DD;
	Mon, 30 Apr 2007 15:05:08 +0200 (CEST)
Received: from mail.mnc.ch ([127.0.0.1])
	by localhost (mail.test.lan [127.0.0.1]) (amavisd-new,
	port 10025) with ESMTP
	id 19071-10; Mon, 30 Apr 2007 15:05:07 +0200 (CEST)
Received: from meuh.mnc.lan (62-2-77-204.static.cablecom.ch [62.2.77.204])
	by mail.mnc.ch (Postfix) with ESMTP id 4937BA829A;
	Mon, 30 Apr 2007 15:05:07 +0200 (CEST)
Received: by meuh.mnc.lan (Postfix, from userid 1000)
	id 675AE18374F; Mon, 30 Apr 2007 15:05:06 +0200 (CEST)
To: Andreas Haumer <andreas@xss.co.at>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query performance problems with partitioned tables
References: <4635D678.3050205@xss.co.at>
X-Hashcash: 
 1:20:070430:andreas@xss.co.at::g8Q9hgb9y6QZ/oSw:00000000000000000000000000000000000000000000Jdk1
X-Hashcash: 
 1:20:070430:pgsql-performance@postgresql.org::SsvhEA1qqYvGip/d:000000000000000000000000000002ki+
From: Guillaume Cottenceau <gc@mnc.ch>
Date: 30 Apr 2007 15:05:06 +0200
In-Reply-To: <4635D678.3050205@xss.co.at>
Message-ID: <87ps5mgghp.fsf@meuh.mnc.lan>
Lines: 48
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.4
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Virus-Scanned: amavisd-new at mnc.ch
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.186 tagged_above=0 required=5 tests=AWL=0.050,
	BAYES_50=0.001, FORGED_RCVD_HELO=0.135
X-Spam-Level: 
X-Archive-Number: 200704/500
X-Sequence-Number: 24377

Andreas Haumer <andreas 'at' xss.co.at> writes:

[...]

> testdb_part=> explain analyze select ts from mwdb.t_mv where zr=3622 and ts < '2007-04-22 00:00:00' order by ts desc limit 1;
>                                                                           QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Limit  (cost=23985.83..23985.83 rows=1 width=8) (actual time=230.100..230.102 rows=1 loops=1)
>    ->  Sort  (cost=23985.83..24019.84 rows=13605 width=8) (actual time=230.095..230.095 rows=1 loops=1)
>          Sort Key: mwdb.t_mv.ts
>          ->  Result  (cost=0.00..23051.72 rows=13605 width=8) (actual time=0.154..177.519 rows=15810 loops=1)
>                ->  Append  (cost=0.00..23051.72 rows=13605 width=8) (actual time=0.149..114.186 rows=15810 loops=1)
>                      ->  Index Scan using pk_mv_zr_ts on t_mv  (cost=0.00..8.27 rows=1 width=8) (actual time=0.047..0.047 rows=0 loops=1)
>                            Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestamp with time zone))
>                      ->  Index Scan using pk_mv_200507 on t_mv_200507 t_mv  (cost=0.00..2417.53 rows=1519 width=8) (actual time=0.095..2.419 rows=744 loops=1)
>                            Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestamp with time zone))

[...]

>                            Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestamp with time zone))
>                      ->  Index Scan using pk_mv_200704 on t_mv_200704 t_mv  (cost=0.00..814.38 rows=378 width=8) (actual time=0.050..1.406 rows=504 loops=1)
>                            Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestamp with time zone))
>  Total runtime: 231.730 ms
> (52 rows)
> 
> Oops!
> Compare the costs or the actual query time between those queries!

Well, I'd say that scanning all partitions until the partition
containing april 2007, when one of the query parameter is having
timestamp before april 2007 but without an initial timestamp
limit, looks normal :)


[...]

> Now my question is: Does the query planner in the case of partitioned tables
> really have to scan all indexes in order to get the next timestamp smaller
> (or larger) than a given one?

Well, how can the planner know inside which partition the wanted
row is? There might be no data, say, inside a couple of
partitions in the past before finding the wanted row, in which
case 3 partitions in the past must be scanned.

-- 
Guillaume Cottenceau, MNC Mobile News Channel SA, an Alcatel-Lucent Company
Av. de la Gare 10, 1003 Lausanne, Switzerland - direct +41 21 317 50 36