Re: need some help with a delete statement - Mailing list pgsql-sql

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.