pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jim C. Nasby <jim@nasby.net>
To: pgsql-performance@postgresql.org
Subject: Automatic analyze on select into
Date: Sat, 26 Apr 2003 10:15:37 -0500
Message-ID: <20030426101537.F66185@flake.decibel.org> (raw)

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?"




view thread (4+ messages)  latest in thread

Message-ID: <20030426101537.F66185@flake.decibel.org>
Permalink:  ../20030426101537.F66185@flake.decibel.org/
Also on:    postgresql.org/message-id/20030426101537.F66185@flake.decibel.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: jim@nasby.net
  Subject: Re: Automatic analyze on select into
  In-Reply-To: <20030426101537.F66185@flake.decibel.org>
  References: <20030426101537.F66185@flake.decibel.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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