From timh@lucidview.net Wed Jul  1 17:16:25 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 45007634CF8
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed,  1 Jul 2009 14:16:25 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 92237-10 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed,  1 Jul 2009 14:16:21 -0300 (ADT)
X-Greylist: delayed 00:33:34.623126 by SQLgrey-1.7.6
Received: from labmail.lucidview.net (labmail.lucidview.net [196.213.94.50])
	by mail.postgresql.org (Postfix) with ESMTP id 17AFA634839
	for <pgsql-sql@postgresql.org>; Wed,  1 Jul 2009 14:16:21 -0300 (ADT)
Received: from mail.lucidview.net ([192.168.133.216])
	by labmail.lucidview.net (8.14.3/8.14.3) with ESMTP id n61GfFiS023687
	for <pgsql-sql@postgresql.org>; Wed, 1 Jul 2009 18:41:16 +0200
Received: from [192.168.2.145] (unknown [192.168.133.1])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	(Authenticated sender: timh_mail@lucidview.net)
	by mail.lucidview.net (Postfix) with ESMTPSA id 5FFBDD80CC
	for <pgsql-sql@postgresql.org>; Wed,  1 Jul 2009 18:42:41 +0200 (SAST)
Message-ID: <4A4B91FF.8040308@lucidview.net>
Date: Wed, 01 Jul 2009 18:42:39 +0200
From: Tim Haak <timh@lucidview.net>
User-Agent: Thunderbird 2.0.0.22 (X11/20090608)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Partitioned tables not using index for min and max 8.2.7?
Content-Type: multipart/mixed; boundary="------------050700080906090504030002"
X-Diffserv: 4
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.411 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001, SUBJECT_FUZZY_TION=0.41
X-Spam-Level: 
X-Archive-Number: 200907/5
X-Sequence-Number: 32746

This is a multi-part message in MIME format.
--------------050700080906090504030002
Content-Type: multipart/alternative;
	boundary="------------040301080204050204040705"


--------------040301080204050204040705
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi

I am running the following query again a partitioned table in 8.2.7. It 
does index scans which is unexpected as there are indexes for the 
log_date column.

min(log_date) from data.table

"Aggregate  (cost=739932.02..739932.02 rows=1 width=8)"
"  ->  Append  (cost=0.00..685106.21 rows=21930321 width=8)"
"        ->  Seq Scan on table  (cost=0.00..33827.10 rows=1215710 width=8)"
"        ->  Seq Scan on table_yy2009mm03 table  (cost=0.00..88056.39 
rows=438839 width=8)"
"        ->  Seq Scan on table_yy2009mm04 table  (cost=0.00..204606.67 
rows=7344967 width=8)"
"        ->  Seq Scan on table_yy2009mm05 table  (cost=0.00..159210.91 
rows=5735091 width=8)"
"        ->  Seq Scan on table_yy2009mm06 table  (cost=0.00..199393.74 
rows=7195574 width=8)"
"        ->  Seq Scan on table_yy2009mm07 table  (cost=0.00..11.40 
rows=140 width=8)"

though if i run it only agains the one table it is significantly faster 
and uses the index

select min(log_date) from only data.table

"Result  (cost=0.06..0.07 rows=1 width=0)"
"  InitPlan"
"    ->  Limit  (cost=0.00..0.06 rows=1 width=8)"
"          ->  Index Scan using idx_table_log_date_only on table  
(cost=0.00..68272.93 rows=1215710 width=8)"
"                Filter: (log_date IS NOT NULL)"

Am I doing something wrong or is this expected.

I tried the old method of

SELECT col FROM table ORDER BY col DESC LIMIT 1

But it does not work either.



-- 
Tim Haak
Email:  timh@lucidview.net
Tel:      +27 12 658 9019


--------------040301080204050204040705
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
</head>
<body bgcolor="#ccffff" text="#000000">
<font size="-1"><font face="Helvetica, Arial, sans-serif">Hi<br>
<br>
I am running the following query again a partitioned table in 8.2.7. It
does index scans which is unexpected as there are indexes for the
log_date column.<br>
<br>
min(log_date) from data.table<br>
<br>
"Aggregate&nbsp; (cost=739932.02..739932.02 rows=1 width=8)"<br>
"&nbsp; -&gt;&nbsp; Append&nbsp; (cost=0.00..685106.21 rows=21930321 width=8)"<br>
"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Seq Scan on table&nbsp; (cost=0.00..33827.10 rows=1215710
width=8)"<br>
"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Seq Scan on table_yy2009mm03 table&nbsp;
(cost=0.00..88056.39 rows=438839 width=8)"<br>
"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Seq Scan on table_yy2009mm04 table&nbsp;
(cost=0.00..204606.67 rows=7344967 width=8)"<br>
"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Seq Scan on table_yy2009mm05 table&nbsp;
(cost=0.00..159210.91 rows=5735091 width=8)"<br>
"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Seq Scan on table_yy2009mm06 table&nbsp;
(cost=0.00..199393.74 rows=7195574 width=8)"<br>
"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Seq Scan on table_yy2009mm07 table&nbsp; (cost=0.00..11.40
rows=140 width=8)"<br>
<br>
though if i run it only agains the one table it is significantly faster
and uses the index<br>
<br>
select min(log_date) from only data.table<br>
<br>
"Result&nbsp; (cost=0.06..0.07 rows=1 width=0)"<br>
"&nbsp; InitPlan"<br>
"&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Limit&nbsp; (cost=0.00..0.06 rows=1 width=8)"<br>
"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Index Scan using idx_table_log_date_only on table&nbsp;
(cost=0.00..68272.93 rows=1215710 width=8)"<br>
"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Filter: (log_date IS NOT NULL)"<br>
<br>
Am I doing something wrong or is this expected.<br>
<br>
I tried the old method of </font></font><br>
<pre class="programlisting">SELECT col FROM table ORDER BY col DESC LIMIT 1

But it does not work either.
</pre>
<font size="-1"><font face="Helvetica, Arial, sans-serif"><br>
<br>
</font></font>
<pre class="moz-signature" cols="72">-- 
Tim Haak
Email:  <a class="moz-txt-link-abbreviated" href="mailto:timh@lucidview.net">timh@lucidview.net</a>
Tel:      +27 12 658 9019
</pre>
</body>
</html>

--------------040301080204050204040705--

--------------050700080906090504030002
Content-Type: text/x-vcard; charset=utf-8;
 name="timh.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="timh.vcf"

begin:vcard
fn:Timothy Haak
n:Haak;Timothy
email;internet:timh@lucidview.net
tel;work:+27 12 658 9019
tel;cell:+27 83 778 7100
x-mozilla-html:TRUE
version:2.1
end:vcard


--------------050700080906090504030002--



From tgl@sss.pgh.pa.us Wed Jul  1 17:44:57 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 752DB6333D4
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed,  1 Jul 2009 14:44:57 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 89516-07 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed,  1 Jul 2009 14:44:56 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by mail.postgresql.org (Postfix) with ESMTP id 80FC9633208
	for <pgsql-sql@postgresql.org>; Wed,  1 Jul 2009 14:44:56 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id n61Hikp4013725;
	Wed, 1 Jul 2009 13:44:46 -0400 (EDT)
To: Tim Haak <timh@lucidview.net>
cc: pgsql-sql@postgresql.org
Subject: Re: Partitioned tables not using index for min and max 8.2.7? 
In-reply-to: <4A4B91FF.8040308@lucidview.net> 
References: <4A4B91FF.8040308@lucidview.net>
Comments: In-reply-to Tim Haak <timh@lucidview.net>
	message dated "Wed, 01 Jul 2009 18:42:39 +0200"
Date: Wed, 01 Jul 2009 13:44:46 -0400
Message-ID: <13724.1246470286@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.41 tagged_above=0 required=5
	tests=SUBJECT_FUZZY_TION=0.41
X-Spam-Level: 
X-Archive-Number: 200907/7
X-Sequence-Number: 32748

Tim Haak <timh@lucidview.net> writes:
> I am running the following query again a partitioned table in 8.2.7. It 
> does index scans which is unexpected as there are indexes for the 
> log_date column.

The index min/max optimization only works on single tables at the
moment.  Sorry.

			regards, tom lane



From timh@lucidview.net Thu Jul  2 09:31:22 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id D35F56333D4
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu,  2 Jul 2009 06:31:22 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 93853-04 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu,  2 Jul 2009 06:31:16 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from labmail.lucidview.net (labmail.lucidview.net [196.213.94.50])
	by mail.postgresql.org (Postfix) with ESMTP id E33FA6333B2
	for <pgsql-sql@postgresql.org>; Thu,  2 Jul 2009 06:31:19 -0300 (ADT)
Received: from mail.lucidview.net ([192.168.133.216])
	by labmail.lucidview.net (8.14.3/8.14.3) with ESMTP id n629TfgN017353;
	Thu, 2 Jul 2009 11:29:41 +0200
Received: from [192.168.4.145] (unknown [192.168.133.1])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	(Authenticated sender: timh_mail@lucidview.net)
	by mail.lucidview.net (Postfix) with ESMTPSA id 8A8C3D80CC;
	Thu,  2 Jul 2009 11:31:07 +0200 (SAST)
Message-ID: <4A4C7E58.4010709@lucidview.net>
Date: Thu, 02 Jul 2009 11:31:04 +0200
From: Tim Haak <timh@lucidview.net>
User-Agent: Thunderbird 2.0.0.22 (X11/20090608)
MIME-Version: 1.0
CC: pgsql-sql@postgresql.org
Subject: Re: Partitioned tables not using index for min and max 8.2.7?
References: <4A4B91FF.8040308@lucidview.net> <13724.1246470286@sss.pgh.pa.us>
In-Reply-To: <13724.1246470286@sss.pgh.pa.us>
Content-Type: multipart/mixed; boundary="------------030801050703050601060705"
X-Diffserv: 4
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.201 tagged_above=0 required=5 tests=AWL=-0.790,
	MISSING_HEADERS=1.581, SUBJECT_FUZZY_TION=0.41
X-Spam-Level: *
X-Archive-Number: 200907/9
X-Sequence-Number: 32750

This is a multi-part message in MIME format.
--------------030801050703050601060705
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Cool thanks then not something I'm doing wrong :). Is this going to be 
changed or is changed in a latter version of postgres. (Do I need to do 
and upgrade or write a work around :) )

Tom Lane wrote:
> Tim Haak <timh@lucidview.net> writes:
>   
>> I am running the following query again a partitioned table in 8.2.7. It 
>> does index scans which is unexpected as there are indexes for the 
>> log_date column.
>>     
>
> The index min/max optimization only works on single tables at the
> moment.  Sorry.
>
> 			regards, tom lane
>
>   

-- 
Tim Haak
Email:  timh@lucidview.net
Tel:      +27 12 658 9019


--------------030801050703050601060705
Content-Type: text/x-vcard; charset=utf-8;
 name="timh.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="timh.vcf"

begin:vcard
fn:Timothy Haak
n:Haak;Timothy
email;internet:timh@lucidview.net
tel;work:+27 12 658 9019
tel;cell:+27 83 778 7100
x-mozilla-html:TRUE
version:2.1
end:vcard


--------------030801050703050601060705--