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--