On 19/04/2019 01:24, Ron wrote:
On 4/18/19 2:14 AM, Andreas Kretschmer wrote:


Am 18.04.19 um 08:52 schrieb rihad:
Hi. Say there are 2 indexes:

    "foo_index" btree (foo_id)

    "multi_index" btree (foo_id, approved, expires_at)


foo_id is an integer. Some queries involve all three columns in their WHERE clauses, some involve only foo_id. Would it be ok from general performance standpoint to remove foo_index and rely only on multi_index? I know that PG would have to do less work updating just one index compared to updating them both, but wouldn't searches on foo_id alone become slower?

it depends .

it depends on the queries you are using, on your workload. a multi-column-index will be large than an index over just one column,
therefore you will have more disk-io when you read from such an index.

But two indexes are larger than one index, and updating two indexes requires more disk IO than updating one index.

Agreed.

A key question would be: how often is the query run, compared to the frequency Insertions, Updates, and Deletions -- wrt the table.


(Prefix compression would obviate the need for this question. Then your multi-column index would be *much* smaller.)

True, but a multi column index will still be bigger than single column index.

[...]


Reply via email to