pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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: Tue, 1 Jul 2003 14:13:31 -0600 (MDT)
Message-ID: <Pine.LNX.4.33.0307011413020.17330-100000@css120.ihs.com> (raw)
In-Reply-To: <25F95360-ABED-11D7-AB18-000393669C1A@poindextrose.org>

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.  




view thread (9+ messages)

Message-ID: <Pine.LNX.4.33.0307011413020.17330-100000@css120.ihs.com>
Permalink:  ../Pine.LNX.4.33.0307011413020.17330-100000@css120.ihs.com/
Also on:    postgresql.org/message-id/Pine.LNX.4.33.0307011413020.17330-100000@css120.ihs.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  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.0307011413020.17330-100000@css120.ihs.com>
  References: <25F95360-ABED-11D7-AB18-000393669C1A@poindextrose.org> <Pine.LNX.4.33.0307011413020.17330-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