Message-ID: <43184B8F.2090505@beccati.com>
Date: Fri, 02 Sep 2005 14:54:39 +0200
From: Matteo Beccati <php@beccati.com>
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
MIME-Version: 1.0
To: Simon Riggs <simon@2ndquadrant.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: ORDER BY and LIMIT not propagated on inherited
References: <43182789.4070307@beccati.com>
	<1125664532.3956.278.camel@localhost.localdomain>
In-Reply-To: <1125664532.3956.278.camel@localhost.localdomain>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Simon Riggs wrote:
>>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.

In this cases the tuple_fraction is useless if the planner doesn't know 
that a ORDER BY on each child table is requested. In fact the sort is 
applied after all the rows are appended. The correct strategy IMHO would 
be applying the order by and limit for each child table (which results 
in an index scan, if possible), appending, then finally sorting a bunch 
of rows, and limiting again.

Every table has indexes, as you can see in the third attacheed EXPLAIN 
ANALYZE output.


Best regards
--
Matteo Beccati
http://phpadsnew.com
http://phppgads.com