SEO

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?
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)
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"

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.
Best regards

paolo



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On N, 2005-09-29 at 11:24 +0200, pmagnoli@systemevolution.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@skype.net>


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings