Re: How should we design our tables and indexes

On 2024-02-12 11:46:35 -0500, Greg Sabino Mullane wrote:
>     If PR_ID is a must in the Join criteria between these table tables table1,
>     table2 in all the queries, then is  it advisable to have a composite index
>     like (pr_id, mid), (pr_id,cid) etc rather than having index on individual
>     columns?
>
>
> No - individual indexes are better, and Postgres has no problem combining them
> when needed.

I'm a bit unsure if I should mention this as veem probably benefits more
from hard and simple rules than more nuanced answers, but that really
depends on the type of query.

For some kinds of queries a composite index can be dramatically faster.
While Postgres can combine indexes that means scanning both indexes and
combining the result, which may need a lot more disk I/O than scanning a
composite index. Indeed, in the cases where a composite index would be
useful but doesn't exist, PostgreSQL usually just chooses the best of
the single column indexes and ignores the rest.

That said, my rule of thumb is to create just single column indexes at
first and only create composite indexes if they are necessary.

        hp


--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"