From: | Harald Fuchs <hari(dot)fuchs(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Multicolumn index for single-column queries? |
Date: | 2019-04-18 13:47:25 |
Message-ID: | 87muknwhle.fsf@protecting.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Andreas Kretschmer <andreas(at)a-kretschmer(dot)de> 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Ron | 2019-04-18 14:12:31 | Re: Multicolumn index for single-column queries? |
Previous Message | Tom Lane | 2019-04-18 13:46:55 | Re: PostgreSQL ping/pong to client |