Differential D8760 Diff 31679 swh/storage/sql/60-indexes.sql

Changeset View

Standalone View

View Options

swh/storage/sql/60-indexes.sql

Show First 20 Lines • Show All 312 Lines • ▼ Show 20 Lines

alter table object_counts add primary key using index object_counts_pkey;

-- object_counts_bucketed

create unique index concurrently object_counts_bucketed_pkey on object_counts_bucketed(line);

alter table object_counts_bucketed add primary key using index object_counts_bucketed_pkey;

-- extid

-- the payload can be null if and only if the payload_type is null

alter table extid

add constraint extid_payload_check

check ((payload_type is null) = (payload is null))

not valid;

-- used to query by (extid_type, extid) + to deduplicate the whole row

create unique index concurrently on extid(extid_type, extid, extid_version, target_type, target);

create unique index concurrently on extid(extid_type, extid, extid_version, target_type, target) where payload_type is null and payload is null;

create unique index concurrently on extid(extid_type, extid, extid_version, target_type, target, payload_type, payload) where payload_type is not null and payload is not null;

olasdUnsubmitted

Not Done

-- used to query by (extid_type, extid) + to deduplicate the whole row

- create unique index concurrently on extid(extid_type, extid, extid_version, target_type, target) where payload_type is null and payload is null;

- create unique index concurrently on extid(extid_type, extid, extid_version, target_type, target, payload_type, payload) where payload_type is not null and payload is not null;

+ create unique index concurrently on extid(extid_type, extid, extid_version, target_type, target, payload_type, payload);

create index concurrently on extid(target_type, target);

Is there any reason to distinguish these two indexes? PostgreSQL can index null columns just fine.

olasd: Is there any reason to distinguish these two indexes? PostgreSQL can index null columns just…

sampletAuthorUnsubmitted

Done

With one index, Postgres will allow duplicate rows (provided the payload is null). This is because it does not treat the null payloads as equal for the purposes of the unique constraint. I mentioned this in my comment here: D8760#228500. To be more concrete, here's an example:

create table test (a integer not null, b integer);
create unique index on test (a, b);
insert into test (a) values (1), (1);
select * from test;

This will result in:

 a | b
---+---
 1 |
 1 |
(2 rows)

Intuitively, you would think it would violate the unique constraint, but it doesn't. Using one index and allowing null payloads amounts to saying "everything with a payload is unique." Using two indexes is like saying "(1) everything without a payload is unique, and (2) everything with a payload is unique."

samplet: With one index, Postgres will allow duplicate rows (provided the payload is null). This is…

olasdUnsubmitted

Not Done

Ah, thanks, I always forget this quirk of postgresql unique indexes.

olasd: Ah, thanks, I always forget this quirk of postgresql unique indexes.

create index concurrently on extid(target_type, target);

vlorentzUnsubmitted

Not Done

hmmmm

Is there no way to use the same index for both?

vlorentz: hmmmm Is there no way to use the same index for both?