DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:mime-version:content-type:message-id:content-transfer-encoding:x-mailer:from:subject:date:to;
	b=F+IGxb6cwW9yBkCpvKoz4/Ak0gnnl4W0ZHVN2TviVmxonO+9wMgKim3m57ishjwU9/vHQmQ0ySm4kuguASqC37nKUtvenLssJnhPVqsFAYLVajUwMHTN4WvzB++fy6y8c5UTEXx1Zzh2Gj23tgeHj/WJ5NOLeFK6GsKzC0EJm3E=
Mime-Version: 1.0 (Apple Message framework v752.3)
Content-Type: text/plain;
	charset=US-ASCII;
	delsp=yes;
	format=flowed
Message-Id: <9BAE31C9-93A6-48AE-AFED-3BB1947D13F1@gmail.com>
Content-Transfer-Encoding: 7bit
From: Jeff Cole <cole.jeff@gmail.com>
Subject: 
Date: Mon, 5 Mar 2007 17:05:41 -0500
To: pgsql-performance@postgresql.org

Hi, I'm new to tuning PostgreSQL and I have a query that gets slower  
after I run a vacuum analyze.  I believe it uses a Hash Join before  
the analyze and a Nested Loop IN Join after.  It seems the Nested  
Loop IN Join estimates the correct number of rows, but underestimates  
the amount of time required.   I am curious why the vacuum analyze  
makes it slower and if that gives any clues as too which parameter I  
should be tuning.

BTW, I know the query could be re-structured more cleanly to remove  
the sub-selects, but that doesn't impact the performance.

thanks,
Jeff


Welcome to psql 8.1.5, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
        \h for help with SQL commands
        \? for help with psql commands
        \g or terminate with semicolon to execute query
        \q to quit

plm_demo=# explain analyze SELECT count(*) AS count_all FROM symptoms  
WHERE ( 1=1 and symptoms.id in (select symptom_id from  
symptom_reports sr where 1=1 and sr.user_id in (select id from users  
where disease_id=1))) ;
                                                                         
        QUERY PLAN
------------------------------------------------------------------------ 
------------------------------------------------------------------------ 
-----------------------
Aggregate  (cost=366.47..366.48 rows=1 width=0) (actual  
time=125.093..125.095 rows=1 loops=1)
    ->  Hash Join  (cost=362.41..366.38 rows=36 width=0) (actual  
time=124.162..124.859 rows=106 loops=1)
          Hash Cond: ("outer".id = "inner".symptom_id)
          ->  Seq Scan on symptoms  (cost=0.00..3.07 rows=107  
width=4) (actual time=0.032..0.295 rows=108 loops=1)
          ->  Hash  (cost=362.25..362.25 rows=67 width=4) (actual  
time=124.101..124.101 rows=106 loops=1)
                ->  HashAggregate  (cost=361.58..362.25 rows=67  
width=4) (actual time=123.628..123.854 rows=106 loops=1)
                      ->  Hash IN Join  (cost=35.26..361.41 rows=67  
width=4) (actual time=9.767..96.372 rows=13074 loops=1)
                            Hash Cond: ("outer".user_id = "inner".id)
                            ->  Seq Scan on symptom_reports sr   
(cost=0.00..259.65 rows=13165 width=8) (actual time=0.029..33.359  
rows=13074 loops=1)
                            ->  Hash  (cost=35.24..35.24 rows=11  
width=4) (actual time=9.696..9.696 rows=1470 loops=1)
                                  ->  Bitmap Heap Scan on users   
(cost=2.04..35.24 rows=11 width=4) (actual time=0.711..6.347  
rows=1470 loops=1)
                                        Recheck Cond: (disease_id = 1)
                                        ->  Bitmap Index Scan on  
users_disease_id_index  (cost=0.00..2.04 rows=11 width=0) (actual  
time=0.644..0.644 rows=2378 loops=1)
                                              Index Cond: (disease_id  
= 1)
Total runtime: 134.045 ms
(15 rows)


plm_demo=# vacuum analyze;
VACUUM
plm_demo=# analyze;
ANALYZE

plm_demo=# explain analyze SELECT count(*) AS count_all FROM symptoms  
WHERE ( 1=1 and symptoms.id in (select symptom_id from  
symptom_reports sr where 1=1 and sr.user_id in (select id from users  
where disease_id=1))) ;
                                                                QUERY  
PLAN
------------------------------------------------------------------------ 
-----------------------------------------------------------------
Aggregate  (cost=586.47..586.48 rows=1 width=0) (actual  
time=3441.385..3441.386 rows=1 loops=1)
    ->  Nested Loop IN Join  (cost=149.05..586.26 rows=85 width=0)  
(actual time=54.517..3441.115 rows=106 loops=1)
          Join Filter: ("outer".id = "inner".symptom_id)
          ->  Seq Scan on symptoms  (cost=0.00..3.08 rows=108  
width=4) (actual time=0.007..0.273 rows=108 loops=1)
          ->  Hash IN Join  (cost=149.05..603.90 rows=13074 width=4)  
(actual time=0.078..24.503 rows=3773 loops=108)
                Hash Cond: ("outer".user_id = "inner".id)
                ->  Seq Scan on symptom_reports sr   
(cost=0.00..258.74 rows=13074 width=8) (actual time=0.003..9.044  
rows=3773 loops=108)
                ->  Hash  (cost=145.38..145.38 rows=1470 width=4)  
(actual time=7.608..7.608 rows=1470 loops=1)
                      ->  Seq Scan on users  (cost=0.00..145.38  
rows=1470 width=4) (actual time=0.006..4.353 rows=1470 loops=1)
                            Filter: (disease_id = 1)
Total runtime: 3441.452 ms
(11 rows)