Christian Rengstl wrote:
Hi everyone,i have a function that looks executes the following command in a while loop in which it iterates through tables (aTable): FOR rec in EXECUTE 'SELECT count(a.allele_1) as c from aTable a INNER JOIN map_table b on(a.snp_id=upper(b.snp_id)) WHERE NOT a.allele_1=a.allele_2 and b.gene=something Unfortunately this command leads to 4 minutes of execution for 15 tables of which each has around 3 Million tuples. I have a partial index on the expression where not allele_1=allele_2 and one on snp_id. Here is the explain i get for the above mentioned command: Aggregate (cost=229621.08..229621.09 rows=1 width=16) -> Merge Join (cost=496.29..229361.10 rows=103991 width=16) Merge Cond: (("outer".snp_id)::text = "inner"."?column2?") -> Index Scan using idx_snpid_pt1 on snp_allel_chr_11pt1 a (cost=0.00..212667.07 rows=2875580 width=29) Filter: ((allele_1)::text <> (allele_2)::text)
Well, it's using the index on snp_id instead, and since you're joining I can see why.
What column(s) do you index with your partial index? If it's snp_id I'd think it odd that it wasn't used.
-- Richard Huxton Archonet Ltd ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
