Re: feeding big script to psql

Tom Lane wrote:
> Peter Wilson <petew(at)yellowhawk(dot)co(dot)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.617 rows=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=1 loops=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) ON UPDATE RESTRICT ON DELETE
RESTRICT
"ca_cos_fk" FOREIGN KEY (instance, client_id, contact_id) REFERENCES contacts(instance, client_id, contact_id) ON UPDATE RESTRICT ON DELETE RESTRICT

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