Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP
	id 41E5F475A9E; Thu, 23 Jan 2003 10:39:43 -0500 (EST)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])
	by postgresql.org (Postfix) with ESMTP
	id 9737A476FAE; Thu, 23 Jan 2003 10:26:22 -0500 (EST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h0NFQJ5u025977;
	Thu, 23 Jan 2003 10:26:19 -0500 (EST)
To: Chantal Ackermann <chantal.ackermann@biomax.de>
Cc: Stephan Szabo <sszabo@megazone23.bigpanda.com>,
	pgsql-general@postgresql.org, pgsql-performance@postgresql.org
Subject: Re: [PERFORM] optimizing query 
In-reply-to: <3E2FB2D1.9020904@biomax.de> 
References: <20030122081422.Y96911-100000@megazone23.bigpanda.com>
	<3E2FB2D1.9020904@biomax.de>
Comments: In-reply-to Chantal Ackermann <chantal.ackermann@biomax.de>
	message dated "Thu, 23 Jan 2003 10:16:01 +0100"
Date: Thu, 23 Jan 2003 10:26:19 -0500
Message-ID: <25976.1043335579@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200301/927
X-Sequence-Number: 36048

Chantal Ackermann <chantal.ackermann@biomax.de> writes:
>   Unique  (cost=359069.64..369948.41 rows=145050 width=33) (actual 
> time=42195.60..43229.04 rows=219435 loops=1)
>     ->  Sort  (cost=359069.64..362695.90 rows=1450503 width=33) (actual 
> time=42195.59..42694.70 rows=695158 loops=1)
>           Sort Key: gene.gene_name, gene_occurrences_puid.puid
>           ->  Merge Join  (cost=63732.51..99264.24 rows=1450503 
> width=33) (actual time=13172.40..27973.79 rows=695158 loops=1)
>                 Merge Cond: ("outer".puid = "inner".puid)
>                 ->  Index Scan using disease_occpd_puid_i on 
> disease_occurrences_puid  (cost=0.00..14543.06 rows=471915 width=4) 
> (actual time=36.50..10916.29 rows=471915 loops=1)
>                 ->  Sort  (cost=63732.51..64580.88 rows=339347 width=29) 
> (actual time=13126.56..14048.38 rows=815068 loops=1)
>                       Sort Key: gene_occurrences_puid.puid
>                       ->  Merge Join  (cost=0.00..22889.19 rows=339347 
> width=29) (actual time=58.00..6775.55 rows=339347 loops=1)
>                             Merge Cond: ("outer".gene_id = "inner".gene_id)
>                             ->  Index Scan using gene_pkey on gene 
> (cost=0.00..7739.91 rows=218085 width=21) (actual time=29.00..3416.01 
> rows=218073
> loops=1)
>                             ->  Index Scan using gene_id_puid_uni on 
> gene_occurrences_puid  (cost=0.00..9525.57 rows=339347 width=8) (actual 
> time=28.69..1936.83 rows=339347 loops=1)
>   Total runtime: 43338.94 msec

Seems like most of the time is going into the sort steps.

> postgresql.conf:
> shared_buffers: 121600
> max_connections: 64
> max_fsm_relations = 200
> max_fsm_pages = 40000
> effective_cache_size = 8000

Try increasing sort_mem.

Also, I'd back off on shared_buffers if I were you.  There's no evidence
that values above a few thousand buy anything.

			regards, tom lane