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?

Community · Accepted Answer · 2017-05-23 12:14:37Z

1

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.

edited May 23, 2017 at 12:14

CommunityBot

11 silver badge

answered Aug 20, 2015 at 6:04

Markus Mikkolainen

3,50720 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mike Dinescu · Accepted Answer · 2015-08-20 06:06:03Z

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, 2015 at 6:06

Mike Dinescu

56k17 gold badges124 silver badges153 bronze badges

Collectives™ on Stack Overflow

How to properly use index on multiple columns

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related