Обсуждение: QUERY PLANNER - Indexe mono column VS composite Index

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)

=========3: without a constraint on tval_char => seq scan========================================================================

 EXPLAIN ANALYSE select  f.patient_num  

from i2b2data_multi_nomi.observation_fact f 

where  

f.concept_cd IN (select concept_cd from  i2b2data_multi_nomi.concept_dimension   where concept_path LIKE '\\i2b2\\cim10\\A00-B99\\%')   

  AND  (  modifier_cd = '@'  AND  valtype_cd = 'T'  ) 

group by  f.patient_num ;

                                                              QUERY PLAN                                                               

---------------------------------------------------------------------------------------------------------------------------------------

HashAggregate  (cost=1305637.84..1305688.23 rows=5039 width=4) (actual time=22689.279..22694.583 rows=16865 loops=1)

   Group Key: f.patient_num

   ->  Hash Join  (cost=4760.13..1297561.67 rows=3230468 width=4) (actual time=12368.418..22674.145 rows=33835 loops=1)

         Hash Cond: ((f.concept_cd)::text = (concept_dimension.concept_cd)::text)

         ->  Seq Scan on observation_fact f  (cost=0.00..1280362.92 rows=3230468 width=14) (actual time=0.226..22004.808 rows=3195625 loops=1)

               Filter: (((modifier_cd)::text = '@'::text) AND ((valtype_cd)::text = 'T'::text))

               Rows Removed by Filter: 41423695

         ->  Hash  (cost=4748.64..4748.64 rows=919 width=10) (actual time=46.833..46.833 rows=925 loops=1)

               Buckets: 1024  Batches: 1  Memory Usage: 39kB

               ->  HashAggregate  (cost=4739.45..4748.64 rows=919 width=10) (actual time=46.196..46.515 rows=925 loops=1)

                     Group Key: (concept_dimension.concept_cd)::text

                     ->  Seq Scan on concept_dimension  (cost=0.00..4734.73 rows=1892 width=10) (actual time=18.899..45.800 rows=925 loops=1)

                           Filter: ((concept_path)::text ~~ '\\i2b2\\cim10\\A00-B99\\%'::text)

                           Rows Removed by Filter: 186413

 Planning time: 1.940 ms

 Execution time: 22695.913 ms

What I would like is the planner allways hit of_idx_modifier