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
Date: Mon, 30 Jun 2003 06:06:25 -0600 (MDT)
Message-ID: <Pine.LNX.4.33.0306300604080.13665-100000@css120.ihs.com> (raw)
In-Reply-To: <6915DFA6-A8F4-11D7-81EC-000393669C1A@poindextrose.org>
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?)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: scott.marlowe@ihs.com, hixson@poindextrose.org
Subject: Re: need some help with a delete statement
In-Reply-To: <Pine.LNX.4.33.0306300604080.13665-100000@css120.ihs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox