pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
To: Harry Broomhall <harry.broomhall@uk.easynet.net>
To: Performance <pgsql-performance@postgresql.org>
Subject: Re: Performance weirdness with/without vacuum analyze
Date: Tue, 21 Oct 2003 17:30:08 +0530
Message-ID: <3F951FC8.2090906@myrealbox.com> (raw)
In-Reply-To: <200310211140.MAA15046@haeb.noc.uk.easynet.net>
References: <200310211140.MAA15046@haeb.noc.uk.easynet.net>

Harry Broomhall wrote:
 > #effective_cache_size = 1000    # typically 8KB each
 > #random_page_cost = 4       # units are one sequential page fetch cost

You must tune the first one at least. Try 
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html to tune these 
parameters.

 >>2) The EXPLAIN ANALYZE of each query instead of just the EXPLAIN
 >
 >
 >   First the case with no vacuum analyze:
 >
 >                                                                       QUERY PLAN
 > 
-------------------------------------------------------------------------------------------------------------------------------------------------------
 >  Merge Join  (cost=99.32..171.32 rows=1000 width=259) (actual 
time=18579.92..48277.69 rows=335671 loops=1)
 >    Merge Cond: ("outer".cdr_id = "inner".cdr_id)
 >    ->  Index Scan using import_cdrs_cdr_id_key on import_cdrs 
(cost=0.00..52.00 rows=1000 width=164) (actual time=0.42..11479.51 rows=335671 
loops=1)
 >    ->  Sort  (cost=99.32..101.82 rows=1000 width=95) (actual 
time=18578.71..21155.65 rows=335671 loops=1)
 >          Sort Key: un.cdr_id
 >          ->  Hash Join  (cost=6.99..49.49 rows=1000 width=95) (actual 
time=4.70..10011.35 rows=335671 loops=1)
 >                Hash Cond: ("outer".interim_cli = "inner".interim_num)
 >                Join Filter: (("outer".starttime >= "inner".starttime) AND 
("outer".starttime <= "inner".endtime))
 >                ->  Seq Scan on import_cdrs un  (cost=0.00..20.00 rows=1000 
width=49) (actual time=0.02..4265.63 rows=335671 loops=1)
 >                ->  Hash  (cost=6.39..6.39 rows=239 width=46) (actual 
time=4.57..4.57 rows=0 loops=1)
 >                      ->  Seq Scan on num_xlate  (cost=0.00..6.39 rows=239 
width=46) (actual time=0.12..2.77 rows=239 loops=1)
 >  Total runtime: 80408.42 msec
 > (12 rows)

You are lucky to get a better plan here because planner is way off w.r.t 
estimated number of rows.
 >
 >   And now the case *with* the vacuum analyze:
 >
 >                                                                QUERY PLAN
 > 
-----------------------------------------------------------------------------------------------------------------------------------------
 >  Hash Join  (cost=15335.91..49619.57 rows=335671 width=202) (actual 
time=12383.44..49297.58 rows=335671 loops=1)
 >    Hash Cond: ("outer".cdr_id = "inner".cdr_id)
 >    ->  Seq Scan on import_cdrs  (cost=0.00..8496.71 rows=335671 width=126) 
(actual time=0.15..9504.24 rows=335671 loops=1)
 >    ->  Hash  (cost=10398.73..10398.73 rows=335671 width=76) (actual 
time=12371.13..12371.13 rows=0 loops=1)
 >          ->  Hash Join  (cost=6.99..10398.73 rows=335671 width=76) (actual 
time=4.91..9412.55 rows=335671 loops=1)
 >                Hash Cond: ("outer".interim_cli = "inner".interim_num)
 >                Join Filter: (("outer".starttime >= "inner".starttime) AND 
("outer".starttime <= "inner".endtime))
 >                ->  Seq Scan on import_cdrs un  (cost=0.00..8496.71 
rows=335671 width=30) (actual time=0.09..3813.54 rows=335671 loops=1)
 >                ->  Hash  (cost=6.39..6.39 rows=239 width=46) (actual 
time=4.71..4.71 rows=0 loops=1)
 >                      ->  Seq Scan on num_xlate  (cost=0.00..6.39 rows=239 
width=46) (actual time=0.22..2.90 rows=239 loops=1)
 >  Total runtime: 432543.73 msec
 > (11 rows)
 >

What happens if you turn off hash joins? Also bump sort memory to something 
good.. around 16MB and see what difference does it make to performance..

  Shridhar





view thread (7+ messages)  latest in thread

Message-ID: <3F951FC8.2090906@myrealbox.com>
Permalink:  ../3F951FC8.2090906@myrealbox.com/
Also on:    postgresql.org/message-id/3F951FC8.2090906@myrealbox.com

 · 

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-performance@postgresql.org
  Cc: shridhar_daithankar@myrealbox.com, harry.broomhall@uk.easynet.net
  Subject: Re: Performance weirdness with/without vacuum analyze
  In-Reply-To: <3F951FC8.2090906@myrealbox.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox