Thread: vacuum analyze hurts performance

Starting with a database where analyze has never been run I get worse
performance after running it -- is there something I'm missing?

Hopefully the log below shows it clearly:

test=> EXPLAIN ANALYZE
SELECT COUNT(DISTINCT "agent_agent"."id")
  FROM "agent_agent" INNER JOIN "auth_user" ON
("agent_agent"."user_id" = "auth_user"."id")
 WHERE ((UPPER("auth_user"."email"::text) LIKE UPPER('%john%')
    OR UPPER("agent_agent"."email_leads"::text) LIKE UPPER('%john%')
    OR UPPER("auth_user"."first_name"::text) LIKE UPPER('%john%')
    OR UPPER("auth_user"."last_name"::text) LIKE UPPER('%john%')
    OR UPPER("agent_agent"."phone_number"::text) LIKE UPPER('%john
%')
    OR UPPER("agent_agent"."personal_statement"::text) LIKE
UPPER('%john%') ));

QUERY
PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=8156.28..8156.29 rows=1 width=4) (actual
time=340.557..340.557 rows=1 loops=1)
   ->  Nested Loop  (cost=0.00..8155.36 rows=369 width=4) (actual
time=1.326..340.346 rows=144 loops=1)
         Join Filter: ((upper((auth_user.email)::text) ~~ '%JOHN
%'::text) OR (upper((agent_agent.email_leads)::text) ~~ '%JOHN
%'::text) OR (upper((auth_user.first_name)::text) ~~ '%JOHN%'::text)
OR (upper((auth_user.last_name)::text) ~~ '%JOHN%'::text) OR
(upper((agent_agent.phone_number)::text) ~~ '%JOHN%'::text) OR
(upper(agent_agent.personal_statement) ~~ '%JOHN%'::text))
         ->  Seq Scan on agent_agent  (cost=0.00..457.44 rows=7844
width=326) (actual time=0.043..8.852 rows=7844 loops=1)
         ->  Index Scan using auth_user_pkey on auth_user
(cost=0.00..0.94 rows=1 width=328) (actual time=0.013..0.014 rows=1
loops=7844)
               Index Cond: (agent_agent.user_id = auth_user.id)
 Total runtime: 340.730 ms
(7 rows)




test=> vacuum analyze agent_agent;
VACUUM
test=> vacuum analyze auth_user;
VACUUM
test=> EXPLAIN ANALYZE
SELECT COUNT(DISTINCT "agent_agent"."id")
  FROM "agent_agent" INNER JOIN "auth_user" ON
("agent_agent"."user_id" = "auth_user"."id")
 WHERE ((UPPER("auth_user"."email"::text) LIKE UPPER('%john%')
    OR UPPER("agent_agent"."email_leads"::text) LIKE UPPER('%john%')
    OR UPPER("auth_user"."first_name"::text) LIKE UPPER('%john%')
    OR UPPER("auth_user"."last_name"::text) LIKE UPPER('%john%')
    OR UPPER("agent_agent"."phone_number"::text) LIKE UPPER('%john
%')
    OR UPPER("agent_agent"."personal_statement"::text) LIKE
UPPER('%john%') ));

QUERY
PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4539.73..4539.74 rows=1 width=4) (actual
time=448.742..448.742 rows=1 loops=1)
   ->  Hash Join  (cost=877.49..4538.80 rows=369 width=4) (actual
time=28.144..448.426 rows=144 loops=1)
         Hash Cond: (auth_user.id = agent_agent.user_id)
         Join Filter: ((upper((auth_user.email)::text) ~~ '%JOHN
%'::text) OR (upper((agent_agent.email_leads)::text) ~~ '%JOHN
%'::text) OR (upper((auth_user.first_name)::text) ~~ '%JOHN%'::text)
OR (upper((auth_user.last_name)::text) ~~ '%JOHN%'::text) OR
(upper((agent_agent.phone_number)::text) ~~ '%JOHN%'::text) OR
(upper(agent_agent.personal_statement) ~~ '%JOHN%'::text))
         ->  Seq Scan on auth_user  (cost=0.00..1733.37 rows=54837
width=48) (actual time=0.007..35.345 rows=54837 loops=1)
         ->  Hash  (cost=457.44..457.44 rows=7844 width=307) (actual
time=26.044..26.044 rows=7844 loops=1)
               ->  Seq Scan on agent_agent  (cost=0.00..457.44
rows=7844 width=307) (actual time=0.024..11.615 rows=7844 loops=1)
 Total runtime: 449.260 ms
(8 rows)

aderose <aderose@tripology.com> writes:
> Starting with a database where analyze has never been run I get worse
> performance after running it -- is there something I'm missing?

Well, not basing such a sweeping statement on a single query example
would be a good start ;-).  This particular plan might have got a little
worse but I'm sure some further investigation would show other cases
that got better.

If you want to nudge it back towards the indexscan plan, reducing
random_page_cost a bit would probably do the trick.  But that's
definitely not a parameter you want to adjust on the basis of only
one test case.

            regards, tom lane