CODEWITHSUNDEEP
postgresqlindexingquery-optimization
devinov
devinov

Reputation: 557

Does having a dedicated a single-column index have a substantial performance benefit over a composite index with the same column leading it?

I have a mapping table that looks like

group_id (int)
item_id (int)

there already exists two composite indexes group_id, item_id and item_id, group_id

I'm finding that deleting all records by group_id from the table is very slow (e.g. DELETE FROM table_name WHERE group_id = 1). From what I've read and see by using EXPLAIN the leading column composite index group_id, item_id will get used even though there no single-column index for group_id. I've seen people mention on here you can get even better performance by having a dedicated single-column index on the first column. How much of a performance benefit should I expect? Would it be a marginal improvement or

On a side note I'm also curious if it's the item_id, group_id index that hurting delete performance by needing to clean up indexes.

Upvotes: 0

Views: 47

Answers (1)

jjanes
jjanes

Reputation: 44137

A smaller index might help from being able to more easily fit in cache. But that would help when you are jumping all around the index reading only one row from each spot, not reading a big chunk of adjacent index entries like you are here. Deletes don't incur direct index maintenance cost. They do create work for some future vacuum to clean up, but that doesn't seem to be what is happening here (and it is mostly independent of the number of columns in the index anyway). Whatever is slowing down your delete, it is not this. The biggest culprit for slowing down non-join deletes are triggers and FK constraints.

Upvotes: 3

Related Questions