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@
