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@
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
