On 19/04/2019 01:47, Harald Fuchs wrote:
Andreas Kretschmer <andreas@xxxxxxxxxxxxxxx> writes:
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.
I think it also depends on the average number of rows having the same foo_id.
The number of rows referenced by an index entry for the multi_index will
always be less than or equal to those for the matching foo_index.
Also there will be fewer index entries per block for the multi_index.
Which is why the I/O count will be higher; even in the best case, where
there is an equal row referenced by the index entries.
