On 3/4/15 9:25 AM, Semyon Reyfman wrote:
I have a list-based partition table and I want to prevent scanning of all children tables in a JOIN, which is using the partition column. I know that CHECK constraints are not used in this case
Why do you say that? If properly written and with constraint_exclusion <> 'off' and with good queries they will be.
so I hope that creating an index on the partition column would help. But I am not sure if I should create this index on the master table or on each of children tables. It seems kind of strange to create an index on a children table where all values of the partition column are the same. So will an index on the master table be sufficient in this case?
It won't help unless you actually have data in the master table. Which normally you wouldn't.
Basically, any indexing you do on a partitioned table must be done *on the partitions*, not on the master table.
-- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
