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 18:54:56 +0100
Message-Id: <1125683697.3956.310.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)
> 

Returning to Matteo's original query, what we are saying is that the new
optimization for MIN/MAX queries doesn't work with inherited tables. 

It could do, by running optimize_minmax_aggregates() for each query that
gets planned to see if a better plan exists for each child table.

I think that's a TODO item.

Optimizing ORDER BY and LIMIT down looks like it would be harder to do
in the general case, even if Matteo's simple transform looks good. I'm
not sure it's a very common query type though...

Best Regards, Simon Riggs