Re: need some help with a delete statement

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.