Subject: Re: ORDER BY and LIMIT not propagated on inherited
From: Simon Riggs <simon@2ndquadrant.com>
To: Matteo Beccati <php@beccati.com>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <43182789.4070307@beccati.com>
References: <43182789.4070307@beccati.com>
Content-Type: text/plain
Organization: 2nd Quadrant
Date: Fri, 02 Sep 2005 13:35:32 +0100
Message-Id: <1125664532.3956.278.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Fri, 2005-09-02 at 12:20 +0200, Matteo Beccati wrote:

> I'm using inherited tables to partition some data which can grow very 
> large. Recently I discovered that a simple query that on a regular table 
> would use an index was instead using seq scans (70s vs a guessed 2s).
> The well known query is:
> 
> SELECT foo FROM bar ORDER BY foo DESC LIMIT 1
> 
> (The same applies for SELECT MIN(foo) FROM bar using 8.1)
> 
> 
> The query plan generated when running the query on a table which has 
> inheritance forces the planner to choose a seq_scan for each table. 
> Wouldn't be a good thing to also promote ORDER BYs and LIMITs to each 
> subscan (like WHERE does)?

The tuple_fraction implied by LIMIT is already passed through to each
child table when using an inherited table structure. This would then be
taken into account when plans are made for each child table. I don't
think the situation you observe occurs as a result of query planning.

Do your child tables have indexes on them? Indexes are not inherited
onto child tables, so it is possible that there is no index for the
planner to elect to use.

Best Regards, Simon Riggs