Re: Multicolumn index for single-column queries?

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.

To be more explicit: if you can live with a slightly less efficient
index scan and want fast data modifications, use only the second index.

If you hardly ever update the table, don't mind the wasted space and
want every bit of query speed (data warehouse), having both indexes
might be better.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com