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)
