Message-ID: <44EE4DA1.5060701@commandprompt.com>
Date: Thu, 24 Aug 2006 18:08:49 -0700
From: "Joshua D. Drake" <jd@commandprompt.com>
Organization: Command Prompt, Inc.
User-Agent: Thunderbird 1.5.0.5 (X11/20060728)
MIME-Version: 1.0
To: Sriram Dandapani <sdandapani@counterpane.com>
CC: "Pgsql-Performance (E-mail)" <pgsql-performance@postgresql.org>
Subject: Re: select max(column) from parent table very slow
References: <6992E470F12A444BB787B5C937B9D4DF057500BF@ca-mail1.cis.local>
In-Reply-To: <6992E470F12A444BB787B5C937B9D4DF057500BF@ca-mail1.cis.local>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Sriram Dandapani wrote:
> Parent table has a column say column1 which is indexed (parent table and
> all child tables are indexed on that column)
> 

Do you mean?

select max(foo) from bar;

In older versions of postgresql that would scan the whole table. In 8.1 
and above it doesn't. However, I am guess that since this is a 
partitioned table the planner isn't smart enough to just perform the 
query on each child and a max on the set that is returned. Thus you are 
scanning each table completely.

But that is just a guess.

Joshua D. Drake


>  
> 
> When a select max(column1) is done on parent table..takes a very long
> time to get back with the result
> 
> The same query on a child table gives instantaneous response (the tables
> are quite large appx.each child table has about 20-30 million rows)
> 
>  
> 
> Constraint exclusion is turned on. The column is not the basis for
> partitioning. Postgres 8.1.2


-- 

    === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
    Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/