From: | Nicolas Paris <niparisco(at)gmail(dot)com> |
---|---|
To: | pgsql-performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | QUERY PLANNER - Indexe mono column VS composite Index |
Date: | 2015-07-09 20:34:25 |
Message-ID: | CA+ssMOSn239yo+iJeXRLnsPEmnb-Ai-pQpQrp0=+Yt8wy6WHXA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hello,
My 9.4 database is used as datawharehouse. I can't change the queries
generated.
first index : INDEX COL (A,B,C,D,E)
In case of query based on COL A, the query planner sometimes go to a seq
scan instead of using the first composite index.
The solution is to add a second indexe (redondant)
second index : INDEX COL (A)
In case of query based on COL A, B, C, D, (without E) as well, it doesn't
uses the first index and prefers a seq scan.
I could create a third indexe :
first index : INDEX COL (A,B,C,D)
But I hope there is an other solution for that (table is huge).
It seems that the malus for using composite indexes is high.
Question is : is there a way to make the composite index more attractive to
query planner ? (idealy equivalent to mono column indexe)
Thanks by advance
From | Date | Subject | |
---|---|---|---|
Next Message | Guillaume Lelarge | 2015-07-09 20:49:04 | Re: QUERY PLANNER - Indexe mono column VS composite Index |
Previous Message | Jeff Janes | 2015-07-09 16:45:25 | Re: pg_stat_all_indexes understand |