Re: QUERY PLANNER - Indexe mono column VS composite Index

2015-07-09 22:34 GMT+02:00 Nicolas Paris <niparisco(at)gmail(dot)com>:

> 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)
>
>
There's no way we can answer that without seeing actual queries and query
plans.

--
Guillaume.
http://blog.guillaume.lelarge.info
http://www.dalibo.com