Query performance

[abenstex]

Hi everybody,

I hope somebody can help me out. Despite having indices on all fields involved in the WHERE clause the query takes just too long: Here's the query:

SELECT a.allele_1, a.allele_2, a.snp_id FROM my_table a
INNER JOIN another_table b USING (snp_id)
WHERE NOT b.snp_id=null AND b.gene='SomeText' AND NOT a.allele_1='-'
ORDER BY b.snp_id

And here's the output of explain analyze:

Sort (cost=92911.05..92912.91 rows=743 width=36) (actual time=22628.564..22634.115 rows=15059 loops=1)
Sort Key: b.snp_id
-> Hash Join (cost=481.81..92875.62 rows=743 width=36) (actual time=5532.788..22528.250 rows=15059 loops=1)
Hash Cond: (("outer".snp_id)::text = ("inner".snp_id)::text)
-> Seq Scan on snp_allel_chr_20_kora_pt6 a (cost=0.00..78008.00 rows=2875676 width=23) (actual time=33.877..20946.906 rows=2876528 loops=1)
Filter: ((allele_1)::text <> '-'::text)
-> Hash (cost=481.48..481.48 rows=132 width=13) (actual time=0.367..0.367 rows=65 loops=1)
-> Bitmap Heap Scan on snps_map b (cost=2.47..481.48 rows=132 width=13) (actual time=0.162..0.297 rows=65 loops=1)
Recheck Cond: ((gene)::text = 'C20orf19'::text)
Filter: (NOT (snp_id IS NULL))
-> Bitmap Index Scan on idx_snps_map_gene (cost=0.00..2.47 rows=135 width=0) (actual time=0.138..0.138 rows=67 loops=1)
Index Cond: ((gene)::text = 'C20orf19'::text)
Total runtime: 22643.329 ms

For the WHERE clause "not allele_1='-' i have tried both indices with expression (not allele_1='-') and a normal index on that field without any differences.

 

[ceco]

as a first tip
consider instead of

NOT b.snp_id=null

to use

b.snp_id IS NOT NULL

I suppose this query doesn't even return the expected result

 

[ceco]

sory this is not the problem, you probaly have turned this syntax on from postgresql.conf

 

[ceco]

I would suggest creating index on
snp_allel_chr_20_kora_pt6(snd_ip) WHERE
NOT a.allele_1='-'
and then ordering BY a.snd._ip
not like you do now with b.snd_ip