query slows down after vacuum analyze

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 seem to 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)



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux