Date: Mon, 30 Jun 2003 06:06:25 -0600 (MDT)
From: "scott.marlowe" <scott.marlowe@ihs.com>
To: Matthew Hixson <hixson@poindextrose.org>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: need some help with a delete statement
In-Reply-To: <6915DFA6-A8F4-11D7-81EC-000393669C1A@poindextrose.org>
Message-ID: <Pine.LNX.4.33.0306300604080.13665-100000@css120.ihs.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

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, 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?

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