Re: Primary Key Increment Doesn't Seem Correct Under Table Partition

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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>