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.
--
Christian Rengstl M.A.
Klinik und Poliklinik für Innere Medizin II
Kardiologie - Forschung
Universitätsklinikum Regensburg
B3 1.388
Franz-Josef-Strauss-Allee 11
93053 Regensburg
Tel.: +49-941-944-7230
