Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Query performance

Query performance

Query performance

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.

RE: Query performance

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

RE: Query performance

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

RE: Query performance

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close