agora inbox for pgsql-general@postgresql.org
help / color / mirror / Atom feedFrom: Tom Lane <tgl@sss.pgh.pa.us>
To: Chantal Ackermann <chantal.ackermann@biomax.de>
Cc: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: pgsql-general@postgresql.org
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] optimizing query
Date: Thu, 23 Jan 2003 10:26:19 -0500
Message-ID: <25976.1043335579@sss.pgh.pa.us> (raw)
In-Reply-To: <3E2FB2D1.9020904@biomax.de>
References: <20030122081422.Y96911-100000@megazone23.bigpanda.com>
<3E2FB2D1.9020904@biomax.de>
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
view thread (41+ messages) latest in thread
Message-ID: <25976.1043335579@sss.pgh.pa.us>
Permalink: ../25976.1043335579@sss.pgh.pa.us/
Also on: postgresql.org/message-id/25976.1043335579@sss.pgh.pa.us
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: tgl@sss.pgh.pa.us, chantal.ackermann@biomax.de, sszabo@megazone23.bigpanda.com, pgsql-performance@postgresql.org
Subject: Re: [PERFORM] optimizing query
In-Reply-To: <25976.1043335579@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox