Re: feeding big script to psql
От | Peter Wilson |
---|---|
Тема | Re: feeding big script to psql |
Дата | |
Msg-id | dcokqn$12d$1@news.hub.org обсуждение исходный текст |
Ответ на | Re: feeding big script to psql (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | 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
В списке pgsql-general по дате отправления: