X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 7BADE30FFE4 for ; Tue, 1 Jul 2003 20:20:12 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 20055-03 for ; Tue, 1 Jul 2003 17:20:02 -0300 (ADT) Received: from mail1.ihs.com (mail1.ihs.com [170.207.70.222]) by svr1.postgresql.org (Postfix) with ESMTP id 7EB2F310D06 for ; Tue, 1 Jul 2003 17:20:01 -0300 (ADT) Received: from css120.ihs.com (css120.ihs.com [170.207.105.120]) by mail1.ihs.com (8.12.9/8.12.9) with ESMTP id h61KJElq000278; Tue, 1 Jul 2003 14:19:14 -0600 (MDT) Date: Tue, 1 Jul 2003 14:13:31 -0600 (MDT) From: "scott.marlowe" To: Matthew Hixson Cc: Subject: Re: need some help with a delete statement In-Reply-To: <25F95360-ABED-11D7-AB18-000393669C1A@poindextrose.org> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-MailScanner: Found to be clean X-MailScanner-SpamCheck: X-Archive-Number: 200307/21 X-Sequence-Number: 14135 On Tue, 1 Jul 2003, Matthew Hixson wrote: > > On Tuesday, July 1, 2003, at 05:47 AM, scott.marlowe wrote: > >>> > >>> what does the output of psql say if you have the /timing switch on? > >> > >> # select cart_id from carts except (select distinct cart_id from > >> cart_contents) limit 1; > >> cart_id > >> --------- > >> 2701 > >> (1 row) > >> Time: 10864.89 ms > >> > >> # explain analyze delete from carts where cart_id=2701; > >> QUERY PLAN > >> ---------------------------------------------------------------------- > >> -- > >> -------------------------------------------- > >> Index Scan using carts_pkey on carts (cost=0.00..3.16 rows=1 > >> width=6) > >> (actual time=0.50..0.52 rows=1 loops=1) > >> Index Cond: (cart_id = 2701) > >> Total runtime: 1.06 msec > >> (3 rows) > >> Time: 257.83 ms > > > > Well, it looks like the fks are running really slow, which may well > > mean > > that they are seq scanning. Examine your table definition and make > > sure > > that they are the same types on both ends, and if not, recreate the > > table > > so that they are either the same types or one is coerced to the other > > when > > referencing it. > > Here are my table definitions. > > # \d v_carts; > Table "public.carts" > Column | Type | Modifiers > -------------+----------------------- > +-------------------------------------------------- > cart_id | integer | not null default > nextval('carts_sequence'::text) > cart_cookie | character varying(24) | > Indexes: carts_pkey primary key btree (cart_id), > cart_cart_cookie btree (cart_cookie) > > # \d cart_contents; > Table "public.cart_contents" > Column | Type | > Modifiers > ------------------+----------------------------- > +---------------------------------------------------------- > cart_contents_id | integer | not null default > nextval('cart_contents_sequence'::text) > cart_id | integer | not null > content_id | integer | not null > expire_time | timestamp without time zone | > Indexes: cart_contents_pkey primary key btree (cart_contents_id), > cart_contents_cart_id btree (cart_id), > cart_contents_content_id btree (content_id) > > > The fk cart_contents.cart_id points to the pk carts.cart_id, and they > are both integers. Try reindexing cart_contents_pkey and carts_pkey and see if that helps. You may have index growth problems. Just guessing.