Message-ID: <4DE61320.6070104@2ndQuadrant.com>
Date: Wed, 01 Jun 2011 06:23:28 -0400
From: Greg Smith <greg@2ndQuadrant.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.16) Gecko/20110505 Icedove/3.0.11
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: Re: Strange behavior of child table.
References: <BANLkTikmOw=wmWe2XkzoaFkRxxSO0hu2Lg@mail.gmail.com>
In-Reply-To: <BANLkTikmOw=wmWe2XkzoaFkRxxSO0hu2Lg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

On 06/01/2011 02:07 AM, Jenish wrote:
> select * from Round_Action where action_id =50000 limit 100 → 
> execution time 80 sec
>
> select * from Round_Action_CH1 action_id =50000 limit 100 → execution 
> time 0.1 sec
>
>

First off:  each of the child tables needs to have the index created on 
them.  That doesn't get inherited just by putting it on the master.

If you already did that, try running these both with "EXPLAIN". Sharing 
the two query plans here would help figure out what's happening.  
Showing the definition of the index on one of the child tables would be 
helpful too.

There are some optimizer limitations in PostgreSQL versions before 9.0 
that prevent it from using an index on the child tables in some 
situations where people expect it to, which includes aggregates like 
MIN/MIN.  I'm not sure if your LIMIT case is running into the same 
issue, the plan will help confirm what's going on.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books