Re: need some help with a delete statement

On Monday, June 30, 2003, at 05:06 AM, scott.marlowe wrote:

> On Fri, 27 Jun 2003, Matthew Hixson wrote:
>
>> 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
>
> While in() is notoriously slow, this sounds more like a problem where  
> your
> query is having to seq scan due to mismatching or missing indexes.
>
> So, what kind of index do you have on cart_id,

Its is a btree index.
                                 Table "public.carts"   Column    |         Type          |
Modifiers
-------------+----------------------- 
+-------------------------------------------------- cart_id     | integer               | not null default  
nextval('carts_sequence'::text) cart_cookie | character varying(24) |
Indexes: v_carts_pkey primary key btree (cart_id),         cart_cart_cookie btree (cart_cookie)


> and what happens if you:
>
> select cart_id from carts except
> (select distinct cart_id from cart_contents) limit 1;
>
> then feed the cart_id into
>
> explain analyze delete from carts where cart_id=id_from_above;
>
> from psql?

#explain analyze delete from carts where cart_id=2700;                                                       QUERY
PLAN
------------------------------------------------------------------------ 
------------------------------------------------ Index Scan using carts_pkey on carts  (cost=0.00..3.16 rows=1 width=6)

 
(actual time=162.14..162.17 rows=1 loops=1)   Index Cond: (cart_id = 2700) Total runtime: 162.82 msec
(3 rows)


>
> Is cart_id a fk to another table (or is another table using it as a  
> fk?)

cart_id is the pk of the carts table.  cart_contents also has a cart_id  
and that is the fk pointing to its entry in the carts table.  There is  
nothing else using cart_id in either of those tables as a fk.  Thanks for the reply,    -M@