Multicolumn index for single-column queries?
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.
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
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
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
On 4/18/19 2:14 AM, 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.
But two indexes are larger than one index, and updating two indexes requires
more disk IO than updating one index.
(Prefix compression would obviate the need for this question. Then your
multi-column index would be *much* smaller.)
--
Angular momentum makes the world go 'round.
On 19/04/2019 01:24, Ron wrote:
On 4/18/19 2:14 AM, 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.But two indexes are larger than one index, and updating two indexes
requires more disk IO than updating one index.
Agreed.
A key question would be: how often is the query run, compared to the
frequency Insertions, Updates, and Deletions -- wrt the table.
(Prefix compression would obviate the need for this question. Then
your multi-column index would be *much* smaller.)
True, but a multi column index will still be bigger than single column
index.
[...]
Andreas Kretschmer <andreas@a-kretschmer.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.
On 4/18/19 8:45 AM, Gavin Flower wrote:
On 19/04/2019 01:24, Ron wrote:
On 4/18/19 2:14 AM, Andreas Kretschmer wrote:
[snip]
(Prefix compression would obviate the need for this question. Then your
multi-column index would be *much* smaller.)True, but a multi column index will still be bigger than single column index.
TANSTAAFL.
--
Angular momentum makes the world go 'round.
On 19/04/2019 02:12, Ron wrote:
On 4/18/19 8:45 AM, Gavin Flower wrote:
On 19/04/2019 01:24, Ron wrote:
On 4/18/19 2:14 AM, Andreas Kretschmer wrote:
[snip]
(Prefix compression would obviate the need for this question. Then
your multi-column index would be *much* smaller.)True, but a multi column index will still be bigger than single
column index.TANSTAAFL.
QUOTE: [Oh, 'tanstaafl.' Means ~There ain't no such thing as a free lunch.']
From The Moon is a Harsh Mistress, by Robert Heinlein. Published 1966
Is where I first came across TANSTAAFL.
However, it appears to have been used at least as early as 1949.
Just adding this, as probably there are many people who don't know the
acronym.
On 19/04/2019 01:47, Harald Fuchs wrote:
Andreas Kretschmer <andreas@a-kretschmer.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.
On 19/04/2019 01:47, Harald Fuchs wrote:
Andreas Kretschmer<andreas@a-kretschmer.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 number of rows referenced by each index entry.
On 19/04/2019 14:01, Gavin Flower wrote:
[...]
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 number of rows referenced by each index entry.
Not sure why my system had this still in my draft folder!
Sorry, for the duplication...