Re: Query performance

Hi,

can you post the complete query,schema- and table-definition,server-version etc. ?
This will help to identity the main problem.

So at the moment i'm just guessing:

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)
This part is very expensive, but i got no clue why.
Maybe the text-type is not so ideal.

Best regards

Hakan Kocaman
Software-Development

digame.de GmbH
Richard-Byrd-Str. 4-8
50829 Köln

Tel.: +49 (0) 221 59 68 88 31
Fax: +49 (0) 221 59 68 88 98
Email: hakan(dot)kocaman(at)digame(dot)de

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of
> Christian Rengstl
> Sent: Thursday, August 03, 2006 10:13 AM
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] Query performance
>
>
> 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
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>