Re: Multicolumn index for single-column queries?

On 18/04/2019 18:52, rihad wrote:
> 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?
>
> Thanks.
>
>
>
The multi column index will require more RAM to hold it.  So if there is 
memory contention, then there would be an increased risk of swapping, 
leading to slower query times.

I suspect that if there is more than enough RAM, then a multi column 
index will be slightly slower than a single column index. However, the 
difference will probably be lost in the noise -- in other words, the 
various things happening in the background will most likely to have far 
more significant impact on query duration.  IMHO


Cheers,
Gavin