Re: need some help with a delete statement

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.  -M@