Sorry. I didn't get all your points. "defining a primary key constraint implicitly creates an index" - Yup. I agree on this. But what is the purpose, for author to explicitly define index for day? CREATE INDEX impressions_by_day_y2012m3ms2_day ON impressions_by_day_y2012m3ms2 (day); Isn't the primary constraint will implicitly create an index for day already? PRIMARY KEY (advertiser_id, day), Thanks. > > > 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 > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
