Re: need some help with a delete statement

On Monday, June 30, 2003, at 12:00 PM, scott.marlowe wrote:

> On Mon, 30 Jun 2003, Matthew Hixson wrote:
>
>> 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)
>
> 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

Thanks,
-M@