From: | Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Multicolumn index for single-column queries? |
Date: | 2019-04-18 23:05:46 |
Message-ID: | a1e6a67c-45fb-b3c0-5718-77ff744b3ecb@archidevsys.co.nz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 19/04/2019 01:47, Harald Fuchs wrote:
> 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.
>
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Souvik Bhattacherjee | 2019-04-19 00:47:43 | Re: Method to pass data between queries in a multi-statement transaction |
Previous Message | Gavin Flower | 2019-04-18 22:58:10 | Re: Multicolumn index for single-column queries? |