Re: Query performance - Mailing list pgsql-general

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.kocaman@digame.de



> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of
> Christian Rengstl
> Sent: Thursday, August 03, 2006 10:13 AM
> To: pgsql-general@postgresql.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
>