Re: Multicolumn index for single-column queries?

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.


Regards, Andreas

-- 
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com