Query performance

[abenstex]

Hi everyone!

i have a table with around 57 million tuples, with the following columns: pid(varchar), crit(varchar), val1(varchar), val2(varchar). Example:
pid crit val1 val2
p1 c1 x y
p1 c2 x z
p1 c3 y x
...
What i am doing is to query all val1 and val2 for one pid and all crit values:

select val1, val2, crit from mytable where pid='somepid' and crit in(select crit from myCritTable);

where myCritTable is a table that contains all crit values (around 42.000) ordered by their insertion date.

                                                                QUERY PLAN

--------------------------------------------------------------------------------
----------------------------------------------------------
 Hash IN Join  (cost=1033.67..134959.41 rows=37120 width=23) (actual time=357.11
6..356984.535 rows=37539 loops=1)
   Hash Cond: (("outer".snp_id)::text = ("inner".snp_id)::text)
   ->  Bitmap Heap Scan on test2  (cost=232.92..132766.66 rows=37120 width=23) (
actual time=291.600..356707.737 rows=37539 loops=1)
         Recheck Cond: ((pid)::text = '1'::text)
         ->  Bitmap Index Scan on idx_test2_pid  (cost=0.00..232.92 rows=37120 w
idth=0) (actual time=234.516..234.516 rows=37539 loops=1)
               Index Cond: ((pid)::text = '1'::text)
   ->  Hash  (cost=700.20..700.20 rows=40220 width=13) (actual time=65.055..65.0
55 rows=40220 loops=1)
         ->  Seq Scan on snps_test  (cost=0.00..700.20 rows=40220 width=13) (act
ual time=0.020..30.131 rows=40220 loops=1)
 Total runtime: 357017.259 ms

Unfortunately the query takes pretty long for the big table, so maybe one of you has a suggestion on how to make it faster

 

[r937]

couple of observations --

this is the ANSI SQL forum, and your question would be better posted in the appropriate forum for your particular database system (which you didn't mention)

the subquery seems unnecessary -- it's apparently implementing "after-the-fact" relational integrity, when what you should have done is use a foreign key to ensure that no tuple gets inserted into mytable with an invalid crit

r937.com | rudy.ca

 

[PHV]

And what about this ?
SELECT A.val1, A.val2, A.crit
FROM mytable A INNER JOIN myCritTable C ON A.crit=C.crit
WHERE pid='somepid'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886