Skip site navigation (1) Skip section navigation (2)

Query performance

From: "Christian Rengstl" <Christian(dot)Rengstl(at)klinik(dot)uni-regensburg(dot)de>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Query performance
Date: 2006-08-03 08:12:49
Message-ID: 44D1CC21.0AD0.0080.0@klinik.uni-regensburg.de (view raw or flat)
Thread:
Lists: pgsql-general
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


Responses

pgsql-general by date

Next:From: Hakan KocamanDate: 2006-08-03 08:34:02
Subject: Re: Query performance
Previous:From: Just SomeoneDate: 2006-08-03 07:10:34
Subject: Re: SELinux + CREATE TABLESPACE = ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group