From: Matteo Beccati <php@beccati.com>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: ORDER BY and LIMIT not propagated on inherited
Date: Fri, 02 Sep 2005 14:54:39 +0200
Message-ID: <43184B8F.2090505@beccati.com> (raw)
In-Reply-To: <1125664532.3956.278.camel@localhost.localdomain>
References: <43182789.4070307@beccati.com>
<1125664532.3956.278.camel@localhost.localdomain>
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.comhttp://phppgads.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: php@beccati.com, simon@2ndquadrant.com
Subject: Re: ORDER BY and LIMIT not propagated on inherited
In-Reply-To: <43184B8F.2090505@beccati.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox