Re: Constraint exclusion, some questions

[Hannu Krosing <hannu(at)skype(dot)net>]

On N, 2005-09-29 at 11:24 +0200, pmagnoli(at)systemevolution(dot)it wrote:
> Hi, I'm testing constraint exclusion on PostgreSQL 8.1 beta 1 on windows with
> the GEOIP countries database and I have a few questions:
> 
> 1. Can I say that an index created on the "parent" table is a "global" index,
> an index that spans over data in all derived tables?

No, postgres has no global indexes yet.

> 2. Can I say that an index created on any of the "child" or "inheriting"
> table is a "partition" index, that spans data belonging that single table
> only?
> (explain plan seems to confirm that, just to be shure)

Yes

> 3. Constraint exclusion works (prunes child tables) only if your query
> contains a condition based on the check constraint (say constraint "A") and
> it doesn't get the fact that the data requested lies just in one child table
> if the condition is on another column (say constraint "B"), even if "global"
> and "partition" indexes are present for constraint "B"

Constraint Exclusion works from constraints only, it does not check
indexes. You can put constraints on any field of a table, not just the
partitioning field, and they all will be used as potential candidates
for CE

> Based on that quick look it would be great to add the ability for the
> optimizer to find the "good" child table from a global index, which would
> make "table partitioning" or "constraint exclusion" even more effective.
> Hope what I wrote makes sense.

Sure, except that currently we donet have global indexes and we dont do
CE based on index values

-- 
Hannu Krosing <hannu(at)skype(dot)net>