Search Postgresql Archives

vacuum analyze hurts performance

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

 



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)


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux