Re: need some help with a delete statement

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?

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