Question 202784 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – in postgres should i use a composite index when i will have queries with just an individual column and then also have queries with both columns

in postgres I will be doing 2 kinds of lookups. first one is by "userId" and second is by "userId" and "abortScript" together.

should i create 2 separate indexes for these columns like this

CREATE INDEX "Category_userId_idx" ON "Category"("userId");
CREATE INDEX "Category_abortScript_idx" ON "Category"("abortScript");

or should i create 1 composite index

CREATE INDEX "Category_userId_abortScript_idx" ON "Category"("userId", "abortScript");

please explain me why.

Tags:

3

Answers


  1. 0

    A single composite index like you describe is the best choice. It can also be used by queries that only have the first column in the WHERE condition.

    Login or Signup to reply.
  2. 0

    Without additional details, it’s impossible to give a definative answer. If there isn’t a need to query efficiently by abortscript independently of userid, then most likely there shouldn’t be an index on just abortscript because doing so would increase the amount of work required for insert, update, and delete operations but would likely not improve query performance. An index on just userid might perform better than a composite or covering index that includes abortscript because approximately twice as many rows would appear in each block of the index which could result in less I/O activity.

    Benchmark each option against live data with realistic queries to determine which works best.

    Login or Signup to reply.
  3. 0

    Depending on the mix of queries with the predicate columns you may want to create the composite index and an individual index on the second column of the composite index. So:

    CREATE INDEX "Category_userId_abortScript_idx" ON "Category"("userId", "abortScript");
CREATE INDEX "Category_abortScript_idx" ON "Category"("abortScript");

    As @JohnH said "Benchmark each option against live data with realistic queries to determine which works best".

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search