Automatic analyze on select into

BEGIN;
SET LOCAL enable_seqscan = off;
SELECT id, team_id, sum(work_units) AS work_units
INTO TEMP email_contrib_summary
FROM email_contrib
WHERE project_id = :ProjectID
GROUP by id, team_id
;
COMMIT;

inserts 29000 rows...

UPDATE email_contrib_summary
SET id = sp.retire_to
FROM stats_participant sp
WHERE sp.id = email_contrib_summary.id
AND sp.retire_to >= 0
AND (sp.retire_date >= (SELECT ps.last_date FROM project_statsrun ps WHERE ps.project_id = :ProjectID)
OR sp.retire_date IS NULL)
;
Nested Loop (cost=0.00..5475.20 rows=982 width=54) (actual time=25.54..2173363.11 rows=29181 loops=1)
InitPlan
-> Seq Scan on project_statsrun ps (cost=0.00..1.06 rows=1 width=4) (actual time=0.06..0.07 rows=1 loops=1)
Filter: (project_id = 8)
-> Seq Scan on email_contrib_summary (cost=0.00..20.00 rows=1000 width=46) (actual time=25.11..1263.26 rows=29753 loops=1)
-> Index Scan using stats_participant__participantretire_id on stats_participant sp (cost=0.00..5.44 rows=1 width=8) (actual time=2.16..72.93 rows=1 loops=29753)
Index Cond: ((sp.retire_to >= 0) AND (sp.id = "outer".id))
Filter: ((retire_date >= $0) OR (retire_date IS NULL))
Total runtime: 2174315.61 msec

GAH! 45 minutes to update 29k rows! BUT, if I do

Hash Join (cost=41104.03..42410.14 rows=29166 width=38) (actual time=8391.81..10925.07 rows=29181 loops=1)
Hash Cond: ("outer".id = "inner".id)
InitPlan
-> Seq Scan on project_statsrun ps (cost=0.00..1.06 rows=1 width=4) (actual time=0.05..0.06 rows=1 loops=1)
Filter: (project_id = 8)
-> Seq Scan on email_contrib_summary (cost=0.00..496.01 rows=29701 width=30) (actual time=0.20..387.95 rows=29753 loops=1)
-> Hash (cost=13939.69..13939.69 rows=394217 width=8) (actual time=8390.72..8390.72 rows=0 loops=1)
-> Seq Scan on stats_participant sp (cost=0.00..13939.69 rows=394217 width=8) (actual time=0.22..5325.38 rows=389115 loops=1)
Filter: ((retire_to >= 0) AND ((retire_date >= $0) OR (retire_date IS NULL)))
Total runtime: 11584.09 msec

Ahhh... soothing relief...

So, question is, would it make sense to automatically do an analyze
after/during a SELECT INTO? Would it be very expensive to analyze the
data as it's being inserted? I think it's pretty well understood that
you want to vacuum/vacuum analyze the entire database regularly, but
that obviously wouldn't help temporary tables... maybe it makes the most
sense to automatically analyze temporary tables only. For that matter,
since temp tables only have one operation performed on them at a time,
maybe it makes sense to keep stats for them up-to-date as part of every
operation?
--
Jim C. Nasby (aka Decibel!) jim(at)nasby(dot)net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"