On 28 Jan 2010, at 2:10, Yan Cheng Cheok wrote: >>> EXECUTE 'CREATE TABLE ' || >> quote_ident(measurement_table_name) || ' >>> ( >>> CONSTRAINT >> pk_measurement_id_' || measurement_table_index || ' PRIMARY >> KEY (measurement_id), >>> CONSTRAINT >> fk_unit_id_' || measurement_table_index || ' FOREIGN KEY >> (fk_unit_id) >>> >> REFERENCES unit (unit_id) MATCH SIMPLE >>> ON >> UPDATE NO ACTION ON DELETE CASCADE >> >>> ) INHERITS >> (measurement);'; >>> EXECUTE 'CREATE INDEX ' || >> quote_ident(measurement_table_name) || '_measurement_id ON ' >> || quote_ident(measurement_table_name) || >> '(measurement_id);'; >> >> >> I think you should actually add the constraints back in >> there, not just create an index. >> > > Thanks. The example I seen here doesn't use "ALERT TABLE" > > http://www.if-not-true-then-false.com/2009/11/howto-create-postgresql-table-partitioning-part-1/ > > But I assume both shall doing the same thing. Sorry, my mistake, must not have had enough coffee yesterday. You _are_ in fact re-defining the primary and foreign keys on your child tables, as you should. Your index threw me off though, as you're adding a second index to the primary key instead of one on the foreign key - and the latter is the one you need. As I wrote before, defining a primary key constraint implicitly creates an index on those columns the primary key is on, so you just created a duplicate index there. >From the page you link to I see how you got the idea that you needed an index - and in your case you probably do, just on a different column. They have a good reason to add an index on their 'day' column - they're partitioning on a date-range on that column and it doesn't have any indexes on it that are usable to query just 'day'[*]. For them it's not their primary key. I think their 'advertiser_id' is in fact a foreign key to another table, but they haven't specified it like that for some reason. I think they should; it's an integer column without a sequence on it and with a not null constraint, it has no meaning by itself so it's clearly referencing some row in another table. *) Indexes on multiple columns can not be used on columns deeper in the index if the query doesn't also query for the higher-up columns. An index on (advertiser_id, day) can not efficiently be used without an advertiser_id to query for days. Advertiser_id is probably a foreign key to another table, so it's not unique by itself and they added the day column to the primary key to make it unique - it's some kind of summary table with a resolution of one day per advertiser, so those together are unique. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4b614e3f10601193912706! -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
