Hi, I have a bunch of records that I need to delete from our database.
These records represent shopping carts for visitors to our website.
The shopping carts I'd like to delete are the ones without anything in
them. Here is the schema:
create sequence carts_sequence;
create table carts(
cart_id integer default nextval('carts_sequence') primary key,
cart_cookie varchar(24));
create sequence cart_contents_sequence;
create table cart_contents(
cart_contents_id integer default nextval('cart_contents_sequence')
primary key,
cart_id integer not null,
content_id integer not null,
expire_time timestamp);
I'm trying to use this query to delete the carts that are not
referenced from the cart_contents table.
delete from carts where cart_id in (select cart_id from carts except
(select distinct cart_id from cart_contents));
My dev machine is running Postgres 7.3.3 and is a 550Mhz Titanium
running MacOS X 10.2.6. It has 1GB of RAM. I have 266777 entries in
v_carts and only 3746 entries in v_cart_contents. Clearly there are a
very large number of empty carts. Running the delete statement above
runs for over 15 minutes on this machine. I just cancelled it because
I want to find a faster query to use in case I ever need to do this
again. While the query is running the disk does not thrash at all. It
is definitely CPU bound. Limiting the statement to 1 item takes about 12 seconds to run:
delete from carts where cart_id in (select cart_id from carts except
(select distinct cart_id from cart_contents) limit 1);
Time: 12062.16 ms
Would someone mind showing me a query that would perform this task a
little faster? Any help would be greatly appreciated. -M@