Re: [PERFORM] optimizing query

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