pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedAutomatic analyze on select into
4+ messages / 2 participants
[nested] [flat]
* Automatic analyze on select into
@ 2003-04-26 15:15 Jim C. Nasby <jim@nasby.net>
0 siblings, 1 reply; 4+ messages in thread
From: Jim C. Nasby @ 2003-04-26 15:15 UTC (permalink / raw)
To: pgsql-performance
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@nasby.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?"
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Automatic analyze on select into
@ 2003-04-26 16:18 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jim C. Nasby <jim@nasby.net>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2003-04-26 16:18 UTC (permalink / raw)
To: jim@nasby.net; +Cc: pgsql-performance
"Jim C. Nasby" <jim@nasby.net> writes:
> So, question is, would it make sense to automatically do an analyze
> after/during a SELECT INTO?
I don't think so. Very often, temp tables are small and only used for
one or two operations anyway --- so the cost of an ANALYZE wouldn't be
repaid. If ANALYZE would be useful, the user can issue one.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Automatic analyze on select into
@ 2003-04-26 18:16 Jim C. Nasby <jim@nasby.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Jim C. Nasby @ 2003-04-26 18:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On Sat, Apr 26, 2003 at 12:18:54PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <jim@nasby.net> writes:
> > So, question is, would it make sense to automatically do an analyze
> > after/during a SELECT INTO?
>
> I don't think so. Very often, temp tables are small and only used for
> one or two operations anyway --- so the cost of an ANALYZE wouldn't be
> repaid. If ANALYZE would be useful, the user can issue one.
Ok, I'll add notes to appropriate pages in the documentation. BTW, do
the notes entered into the interactive docs get rolled into the formal
documentation at any point?
--
Jim C. Nasby (aka Decibel!) jim@nasby.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?"
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Automatic analyze on select into
@ 2003-04-26 18:40 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jim C. Nasby <jim@nasby.net>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2003-04-26 18:40 UTC (permalink / raw)
To: jim@nasby.net; +Cc: pgsql-performance
"Jim C. Nasby" <jim@nasby.net> writes:
> Ok, I'll add notes to appropriate pages in the documentation. BTW, do
> the notes entered into the interactive docs get rolled into the formal
> documentation at any point?
Usually, towards the end of a release cycle, someone will run through
them looking for stuff worth incorporating into the next generation.
It's pretty informal though.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-04-26 18:40 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-26 15:15 Automatic analyze on select into Jim C. Nasby <jim@nasby.net>
2003-04-26 16:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-04-26 18:16 ` Jim C. Nasby <jim@nasby.net>
2003-04-26 18:40 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox