Ok, here is the problem (it's different than what I explained before) ==INDEX == CREATE INDEX of_idx_modifier ON i2b2data_multi_nomi.observation_fact USING btree (concept_cd COLLATE pg_catalog."default", modifier_cd COLLATE pg_catalog."default", valtype_cd COLLATE pg_catalog."default", tval_char COLLATE pg_catalog."default", nval_num);
==QUERY== 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' AND tval_char IN ('DP') ) group by f.patient_num ; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=35153.99..35154.40 rows=41 width=4) (actual time=81.223..82.718 rows=5206 loops=1) Group Key: f.patient_num -> Nested Loop (cost=4740.02..35089.11 rows=25951 width=4) (actual time=45.393..76.893 rows=7359 loops=1) -> HashAggregate (cost=4739.45..4748.64 rows=919 width=10) (actual time=45.097..45.586 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=17.479..44.573 rows=925 loops=1) Filter: ((concept_path)::text ~~ '\\i2b2\\cim10\\A00-B99\\%'::text) Rows Removed by Filter: 186413 -> Index Scan using of_idx_modifier on observation_fact f (cost=0.56..32.86 rows=15 width=14) (actual time=0.025..0.031 rows=8 loops=925) Index Cond: (((concept_cd)::text = (concept_dimension.concept_cd)::text) AND ((modifier_cd)::text = '@'::text) AND ((valtype_cd)::text = 'T'::text) AND ((tval_char)::text = 'DP'::text)) Planning time: 2.843 ms Execution time: 83.273 ms (12 rows) ============2 : without 3 constraint that match index => 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' AND tval_char IN ('DP') ) group by f.patient_num ; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=1345377.85..1346073.80 rows=69595 width=4) (actual time=18043.140..18048.741 rows=16865 loops=1) Group Key: f.patient_num -> Hash Join (cost=4760.13..1233828.53 rows=44619728 width=4) (actual time=17109.041..18027.763 rows=33835 loops=1) Hash Cond: ((f.concept_cd)::text = (concept_dimension.concept_cd)::text) -> Seq Scan on observation_fact f (cost=0.00..1057264.28 rows=44619728 width=14) (actual time=0.040..7918.984 rows=44619320 loops=1) -> Hash (cost=4748.64..4748.64 rows=919 width=10) (actual time=49.523..49.523 rows=925 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 39kB -> HashAggregate (cost=4739.45..4748.64 rows=919 width=10) (actual time=48.806..49.117 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.828..48.191 rows=925 loops=1) Filter: ((concept_path)::text ~~ '\\i2b2\\cim10\\A00-B99\\%'::text) Rows Removed by Filter: 186413 Planning time: 2.588 ms Execution time: 18051.031 ms (14 rows) =========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 Thanks ! 2015-07-09 22:49 GMT+02:00 Guillaume Lelarge <guilla...@lelarge.info>: > 2015-07-09 22:34 GMT+02:00 Nicolas Paris <nipari...@gmail.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 >