How to properly use index on multiple columns

Question

I have this Query in PostgreSQL:

SELECT cte.* 
FROM (

                 select ......
                 from A a
                 left join B b using (id)
                 left join C c on (c.cid=a.cid)
                 left join D d on (d.did=c.did)
                 left join E e on (e.eid=d.eid)
                 left JOIN ( F f
                           JOIN ( SELECT func() AS funcid) x ON f.fid = x.fid) ON a.id = f.id
                 left join G g on (g.gid=c.gid)
                 left join H h on (h.hid=c.hid)
                 where b.userid= first_param 
                 order by .....
) as cte
where cte.issuedate=second_param

This query runs with 2 parameters: first_param is INTEGER and second_param is DATE - Both of them are related to fields of table B

This query generates data to my main work screen so its going to be used many times.

My dilemma is because of the structure of the query since first_param and second_param are in "diffrent levels" of the query I don't know if I should create an index on both columns together or index of each columns separately? Also, the second_param is accessed from the Common Table Expressions and not directly from B

In other words... choose between:

CREATE INDEX name_a
  ON B
  USING btree
  (userid,issuedate);

OR:

CREATE INDEX name_aa
  ON B
  USING btree
  (userid);

CREATE INDEX name_ab
  ON B
  USING btree
  (issuedate);

The guidelines of Indexes says that if we use 2 columns together many time then we should index it together but in this case I'm not so sure...

Can you advise?

score 1 · Accepted Answer · edited May 23 '17 at 12:14

I would probably use two separate indexes, it is more flexible and in most cases just as performant or even more. see for example: 2 PostgreSQL indices on the same column of the same table - redundant?

For example i think that in your query the 2 column index would be relatively useless for the issuedate comparison.

Generally speaking, avoid multicolumn indexes unless you have a very specific case and a reason to use them.

score 1 · Answer 2 · answered Aug 20 '15 at 06:06

1

You are right. You should opt for two separate indexes. You would use a multi-column index if you used the columns in the same join (ie. FROM tableA JOIN tableB ON tableA.columnA1 = tableB.columnB1 AND tableA.columnA2 = tableB.columnB2)

answered Aug 20 '15 at 06:06

Mike Dinescu

54,171
16
118
151

How to properly use index on multiple columns

2 Answers2