Re: [Gnumed-devel] re: optimizations for inheritance searching

[Karsten Hilbert]

On Sun, Mar 12, 2006 at 02:08:27PM +0800, Syan Tan wrote:

> if they fix the postgresql planner, then you can revert to the original sql,
> which is more terse .
> 
> http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html
> 
> Stephen Friedrich <s.friedrich AT eekboom.com>
> 18 Jan 2006 13:41:24
> 
> Another restriction:
> Even if you do define indexes on the inherited tables yourself, often they
> won't be used if you query the parent table.
> Even when constraint exclusion restricts the query to a single inherited 
> table,
> it seems that postgres always tries to merge result rows from the parent table
> (even if it is empty) and the inherited table.
> For example even if both the parent and an inherited table defined an index 
> for
> column "id", then a query on the parent table that includes "order by id" does
> _not_ use the indexes, but a costly sort is done after combining the rows from
> the inherited table and the zero rows from the parent table.
> 
> So performance can be drastically worse if you use partitions.
> 
> 
> I think it applies to inheritance in general as well as for partitioning.

We have already proven this guy wrong. Even if this guy *is*
right, currently, and the planner "forgets" to look at the
possibility of index scans despite it being possible we
should then keep the original, terse sql and use "set
enable_seqscan to off" right before it in the same
transaction to stay as close as possible to how we would
want things to be would the work properly.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346