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

Re: [PERFORM] optimizing query

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Chantal Ackermann <chantal(dot)ackermann(at)biomax(dot)de>
Cc: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>,pgsql-general(at)postgresql(dot)org, pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] optimizing query
Date: 2003-01-23 15:26:19
Message-ID: 25976.1043335579@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-performance
Chantal Ackermann <chantal(dot)ackermann(at)biomax(dot)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

In response to

Responses

pgsql-performance by date

Next:From: John LangeDate: 2003-01-23 15:36:11
Subject: Re: Query plan and Inheritance. Weird behavior
Previous:From: Stephan SzaboDate: 2003-01-23 15:05:28
Subject: Re: optimizing query

pgsql-general by date

Next:From: Andrei Verovski (aka MacGuru)Date: 2003-01-23 15:51:36
Subject: PHP Abstraction Layer - Your Opinion Please
Previous:From: Tom LaneDate: 2003-01-23 15:14:05
Subject: Re: tsearch comments

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