Re: feeding big script to psql - Mailing list pgsql-general

Tom Lane wrote:
> Peter Wilson <petew@yellowhawk.co.uk> writes:
>> I found a while ago that after inserting a lot of rows into a clean
>> Postgres table it would take several minutes just to analyse a command,
>> not even starting the execution.
>
> Oh?  Could you provide a test case for this?  I can certainly believe
> that the planner might choose a bad plan if it has no statistics, but
> it shouldn't take a long time to do it.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
After a vacuum the query plan becomes:

Unique  (cost=1438.65..1438.66 rows=1 width=39) (actual time=260.473..260.489 rows=3 loops=1)
   ->  Sort  (cost=1438.65..1438.65 rows=1 width=39) (actual time=260.468..260.471 rows=3 loops=1)
         Sort Key: c.client_id, c.instance, c.contact_id, c.uname
         ->  Nested Loop  (cost=1434.14..1438.64 rows=1 width=39) (actual time=260.007..260.306 rows=3 loops=1)
               ->  HashAggregate  (cost=1434.14..1434.14 rows=1 width=8) (actual time=259.666..259.686 rows=3 loops=1)
                     ->  Index Scan using ca_pk on contact_att subb  (cost=0.00..1433.95 rows=78 width=8) (actual
time=0.367..259.617rows=3 loops=1) 
                           Index Cond: ((instance = '0'::bpchar) AND ((client_id)::text = 'gadget'::text))
                           Filter: ((contact_id)::numeric = 3854.000000)
               ->  Index Scan using cos_pk on contacts c  (cost=0.00..4.48 rows=1 width=39) (actual time=0.178..0.182
rows=1loops=3) 
                     Index Cond: ((c.instance = '0'::bpchar) AND ((c.client_id)::text = 'gadget'::text) AND
(c.contact_id= "outer".community_id)) 
                     Filter: ((("type")::text = 'COMMUNITY'::text) OR (("type")::text = 'OU'::text) OR (("type")::text
='INDIVIDUAL'::text)) 
Total runtime: 260.886 ms

whitebeam_slow=> \d contacts
                                       Table "public.contacts"
    Column    |          Type           |                         Modifiers
-------------+-------------------------+-----------------------------------------------------------
  instance    | character(1)            | not null
  client_id   | character varying(50)   | not null
  contact_id  | bigint                  | not null default nextval('contacts_contact_id_seq'::text)
  uname       | character varying(32)   | not null
  type        | character varying(20)   | not null default 'INDIVIDUAL'::character varying
  parent      | bigint                  |
  name        | character varying(240)  |
  phone       | character varying(32)   |
  fax         | character varying(32)   |
  email       | character varying(240)  |
  basic_pw    | character varying(128)  |
  description | character varying(240)  |
  custom_data | character varying(8192) |
Indexes:
     "cos_pk" PRIMARY KEY, btree (instance, client_id, contact_id)
     "cos_uk" UNIQUE, btree (instance, client_id, uname)
     "co_pa_ind" btree (parent)
     "co_ty_ind" btree ("type")

whitebeam_slow-> \d contact_att
             Table "public.contact_att"
     Column    |         Type          | Modifiers
--------------+-----------------------+-----------
  instance     | character(1)          | not null
  client_id    | character varying(50) | not null
  contact_id   | bigint                | not null
  community_id | bigint                | not null
  inherited    | smallint              |
Indexes:
     "ca_pk" PRIMARY KEY, btree (instance, client_id, contact_id, community_id)
Foreign-key constraints:
     "ca_cos_comm" FOREIGN KEY (instance, client_id, community_id) REFERENCES contacts(instance, client_id, contact_id)
ONUPDATE RESTRICT ON DELETE  
RESTRICT
     "ca_cos_fk" FOREIGN KEY (instance, client_id, contact_id) REFERENCES contacts(instance, client_id, contact_id) ON
UPDATERESTRICT ON DELETE RESTRICT 


------------------------------------------------------------------------
Peter Wilson - YellowHawk Ltd, http://www.yellowhawk.co.uk