Review composite indexing

[jinjagit]

Review the performance impacts of composite indexing.
Including:

1. whether multiple less complex indices are required when more complex indices already include the indexed fields
2. whether we can improve the ordering off fields in complex indices

Resulting from a discussion on #227

[jinjagit]

Some background, from reading Single vs Composite Indexes in Relational Databases and thinking about how it relates to our use of indices in our api.

TLDR:

• Do not use an index for low-read but high-write tables.

Check. We read tables far more than we write to them. E.g. create vote, vs list_votes vs find vote where organization_id == "9834763425187"

• Do not use an index if the field has low cardinality, the number of distinct values in that field.

Although voting_method might have a low number of distinct values in the uniq_index_org_participant_proposal_voting_method on Votes, we also use this index to enforce uniqueness in the changeset, so I would say this is desirable.

• If we’re going to create an index on field1 but also create an composite index on (field1, field2) . Then creating just the composite index on (field1, field2) is enough since it can be used for querying by field1 alone.

We might well get better performance (faster write times) by removing some composite indices that are 'covered' by more complex composite index/indices. For example for Votes we have:

CREATE UNIQUE INDEX uniq_index_org_participant_proposal_voting_method ON public.votes USING btree (organization_id, participant_id, proposal_url, voting_method_id);
CREATE INDEX votes_organization_id_index ON public.votes USING btree (organization_id);
CREATE INDEX votes_organization_id_participant_id_index ON public.votes USING btree (organization_id, participant_id);
CREATE INDEX votes_organization_id_proposal_url_index ON public.votes USING btree (organization_id, proposal_url);

Perhaps we would get better writing performance from using just the one (unique) index uniq_index_org_participant_proposal_voting_method

• The order of the constituent columns that comprise a composite index is important.

So, for the unique index above, queries will benefit from this if the query order reflects the index order.
E.g. These queries will benefit:

SELECT * FROM table 
WHERE organization_id = 'value';

SELECT * FROM table 
WHERE organization_id = 'value1'
AND participant_id = 'value2'
AND proposal_url = 'value3'
AND voting_method = 'value3'

These will not (as query order does not reflect order of index):

SELECT * FROM table 
WHERE proposal_url = 'value';

SELECT * FROM table 
WHERE voting_method = 'value1'
AND proposal_url = 'value2'

Question: Will not benefit 'as much', or 'not at all'? Experimenting might reveal this.

Note: We can also monitor the query times on Honeycomb to understand our baseline query times + use this telemetry in future reorganizing of the indices.

[jinjagit]

Please feel free to add comments / resources to this issue.

[oliverbarnes]

Thanks, this is a good start. I agree we should benchmark, but we need to compare with different indexes, so while we want to watch Honeycomb, it could be useful to benchmark queries using EXPLAIN. Starting with no index, and then adding indexes with progressively more fields composited.

In the end it might be too early to optimize, because we don't have real usage to see where the most important bottlenecks really are, but it's good to have a good grasp of where we're at with this. A while back I worked on the order of the indexes at least, so that is (hopefully) in good shape.

The thing that prompted me to review the composite indexes are the cases where we're pretty much adding all table fields in the index :) I think it'd be worth looking into this further.

Btw, I think these queries will never happen:

SELECT * FROM table 
WHERE proposal_url = 'value';

SELECT * FROM table 
WHERE voting_method = 'value1'
AND proposal_url = 'value2'

Because organization_id will be part of all queries. Not sure if you were just giving pseudo-query examples to make the point of the index order.

[jinjagit]

Not sure if you were just giving pseudo-query examples to make the point of the index order.

Yes, just to explain the concept (as much to remind myself as anything else). I agree they look well ordered (in our api) at first-glance.

[jinjagit]

The thing that prompted me to review the composite indexes are the cases where we're pretty much adding all table fields in the index :)

Yes, and since we often have a unique index which uses most or all of the fields, I'd be interested in whether benchmarks show this is sufficient / better to use without other / some other indices.

I don't yet know enough about unique indices to say whether they are as performant as standard indices when querying, but I am assuming they are (and that they just are also used to ensure uniqueness when attempting to insert).

[jinjagit]

it could be useful to benchmark queries using EXPLAIN.

Nice. Didn't know about this 👍