Cockroach: sql: implement partial indexes

[knz]

Partial indexes are indexes that only track a subset of all rows in a table.

For example CREATE INDEX foo ON sometable(somecol) WHERE something > blah

Can be combined with #9682 for some nifty index action.

Suggested by @petermattis

[petermattis]

A specific interesting example is not indexing NULL values (i.e. WHERE something IS NOT NULL).

[manuel-huez]

Another use-case we have is to keep some indexes lightweight on big tables. We want to log specific actions done on our platform, and retry them later if some failed. For that purpose, we use partial indexes on postgres to only index items to be retried WHERE processed = false.

[knz]

cc @awoods187 for triage and prioritization

[petermattis]

The building of partial indexes is the "easy" part of this issue. Utilizing the partial index appropriately for reads will require the optimizer to be aware of the partial index. Almost certainly not a candidate for 2.1.

[kocoten1992]

can't resist to post this: https://dba.stackexchange.com/questions/91135/can-postgresql-use-multiple-partial-indexes-per-query

if we could efficiently combine multiple indexes :smile:

[BramGruneir]

There is a workaround but it is not ideal and duplicates some data.

If this is a basic one column constraint, like something > blah:
Create a new computed column that evaluates to the value of the column when the condition is met and otherwise evaluates to null.
Then add a regular unique constraint on that new column.
This works because nulls aren't considered equivalent in unique constraints.

If this the constraint is a bit more complicated, (i.e. involving more than one column):
Create a new computed column that evaluates to true condition is met and otherwise evaluates to null.
Then add a regular unique constraint on the column(s) used in the constraint and that new computed column.
And again this works because nulls aren't considered equivalent in unique constraints.

[knz]

I think something was lost in the design of the workaround. This phrase:

Create a new computed column that evaluates to true condition is met and otherwise evaluates to null.

Should be instead:

Create a new computed column that evaluates to the value of the column-to-index if the condition is met, otherwise evaluates to null

i.e. change create index on foo(x) where y>z

to alter table foo add column x_partial int as (if(y>z, x, null)) stored; create index on foo(x_partial)

[zacps]

Another use case is for conditional unique constraints.

CREATE UNIQUE INDEX partial_unique ON a (col_a) WHERE (col_b is null);

[karlseguin]

For completeness, hopefully partial unique indexes would be usable in anon conflict

[ddrao]

Any update on the partial index feature?

[isoos]

+1 for any update / ETA on this?

@knz, @BramGruneir: assuming we do create a unique constraint like this, can you provide 1-2 queries that can be used to filter on this condition + hopefully join it with another indexed property?

[mgartner]

Any update on the partial index feature?

We're currently working on partial indexes and they should be included in the next release, 20.2.

[mgartner]

Partial indexes have been implemented and will be included in 20.2.

[mgartner]

Note that partial _inverted_ indexes are not currently supported. This is tracked by https://github.com/cockroachdb/cockroach/issues/50952.

[isoos]

@mgartner: I haven't seen any sign in the release notes of 20.2.0-beta.1. Maybe it would be worth to add it there?

[mgartner]

@isoos thanks for pointing this out. The release notes have been updated and partial indexes are now mentioned at the top of the page.