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@