Обсуждение: need some help with a delete statement
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
Would someone mind showing me a query that would perform this task a
little faster? Any help would be greatly appreciated. -M@
On Fri, Jun 27, 2003 at 16:09:31 -0700, Matthew Hixson <hixson@poindextrose.org> 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: IN is slow in 7.3.3 and below. It will be substantially faster in 7.4. In the meantime rewriting your query to use not exists will probably speed things up for you. Delete also allows for joins with other tables which doesn't help in thsi particular case (at least not any way I can think of), but is help for deleting items there are in (as opposed to are not in) another table.
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?)
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@
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,
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@
On Tue, 1 Jul 2003, Matthew Hixson wrote:
>
> 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
Well, it looks like the fks are running really slow, which may well mean
that they are seq scanning. Examine your table definition and make sure
that they are the same types on both ends, and if not, recreate the table
so that they are either the same types or one is coerced to the other when
referencing it.
On Tuesday, July 1, 2003, at 05:47 AM, scott.marlowe wrote:
>>>
>>> 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
>
> Well, it looks like the fks are running really slow, which may well
> mean
> that they are seq scanning. Examine your table definition and make
> sure
> that they are the same types on both ends, and if not, recreate the
> table
> so that they are either the same types or one is coerced to the other
> when
> referencing it.
Here are my table definitions.
# \d v_carts; Table "public.carts" Column | Type |
Modifiers
-------------+-----------------------
+-------------------------------------------------- cart_id | integer | not null default
nextval('carts_sequence'::text) cart_cookie | character varying(24) |
Indexes: carts_pkey primary key btree (cart_id), cart_cart_cookie btree (cart_cookie)
# \d cart_contents; Table "public.cart_contents" Column |
Type | Modifiers
------------------+-----------------------------
+---------------------------------------------------------- cart_contents_id | integer | not null
default
nextval('cart_contents_sequence'::text) cart_id | integer | not null content_id |
integer | not null expire_time | timestamp without time zone |
Indexes: cart_contents_pkey primary key btree (cart_contents_id), cart_contents_cart_id btree (cart_id),
cart_contents_content_id btree (content_id)
The fk cart_contents.cart_id points to the pk carts.cart_id, and they
are both integers. -M@
On Tue, 1 Jul 2003, Matthew Hixson wrote:
>
> On Tuesday, July 1, 2003, at 05:47 AM, scott.marlowe wrote:
> >>>
> >>> 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
> >
> > Well, it looks like the fks are running really slow, which may well
> > mean
> > that they are seq scanning. Examine your table definition and make
> > sure
> > that they are the same types on both ends, and if not, recreate the
> > table
> > so that they are either the same types or one is coerced to the other
> > when
> > referencing it.
>
> Here are my table definitions.
>
> # \d v_carts;
> Table "public.carts"
> Column | Type | Modifiers
> -------------+-----------------------
> +--------------------------------------------------
> cart_id | integer | not null default
> nextval('carts_sequence'::text)
> cart_cookie | character varying(24) |
> Indexes: carts_pkey primary key btree (cart_id),
> cart_cart_cookie btree (cart_cookie)
>
> # \d cart_contents;
> Table "public.cart_contents"
> Column | Type |
> Modifiers
> ------------------+-----------------------------
> +----------------------------------------------------------
> cart_contents_id | integer | not null default
> nextval('cart_contents_sequence'::text)
> cart_id | integer | not null
> content_id | integer | not null
> expire_time | timestamp without time zone |
> Indexes: cart_contents_pkey primary key btree (cart_contents_id),
> cart_contents_cart_id btree (cart_id),
> cart_contents_content_id btree (content_id)
>
>
> The fk cart_contents.cart_id points to the pk carts.cart_id, and they
> are both integers.
Try reindexing cart_contents_pkey and carts_pkey and see if that helps.
You may have index growth problems. Just guessing.