From: William Scott Jordan <wsjordan@brownpapertickets.com>
To: pgsql-sql@postgresql.org
Subject: Re: Resolved - Referencing tables are grinding UPDATE to
Date: Mon, 15 Jan 2007 14:55:28 -0800
Message-ID: <7.0.1.0.2.20070115145412.0545bb68@brownpapertickets.com> (raw)
In-Reply-To: <7.0.1.0.2.20070115124931.06433838@pandimensional.org>
References: <7.0.1.0.2.20070115124931.06433838@pandimensional.org>
Yeah, I forgot about the RULE on that table. Ignore my previous email.
-Scott
At 01:26 PM 1/15/2007, you wrote:
>Hey all!
>
>I'm having some trouble with a simple update on a table that only
>has about 250,000 rows in it. The table itself looks something like:
>
>CREATE TABLE price_details (
>price_detail_id int PRIMARY KEY,
>private bool
>) ;
>
>There is one table that references price_details, but isn't affected
>by the "private" column, and one table that references this second
>table. They look something like:
>
>CREATE TABLE prices (
>p_id int PRIMARY KEY,
>price_detail_id int NOT NULL REFERENCES price_details ON DELETE CASCADE,
>max_sales int
>) ;
>
>CREATE INDEX prices_price_detail_id ON prices (price_detail_id) ;
>
>CREATE TABLE sales (
>sales_id int PRIMARY KEY,
>p_id int NOT NULL REFERENCES prices ON DELETE CASCADE,
>sales int
>) ;
>
>CREATE INDEX sales_p_id ON sales (p_id) ;
>
>I'm trying to do a simple update to the "private" column in the
>price_details table, which I expected to take a few seconds at
>most. After 10 minutes, I gave up and ran explain, with this as the result:
>
>
>EXPLAIN UPDATE price_details SET private = 't' WHERE private = 'f' ;
>----------------------------------------------------------------------------------------
> Nested Loop (cost=2663.45..363527947.70 rows=118759098 width=50)
> Join Filter: (subplan)
> -> Seq Scan on sales (cost=0.00..3685.27 rows=54627 width=42)
> -> Materialize (cost=2663.45..2706.93 rows=4348 width=12)
> -> Seq Scan on price_details (cost=0.00..2663.45
> rows=4348 width=12)
> Filter: (private = false)
> SubPlan
> -> Index Scan using prices_price_detail_id on
> prices (cost=0.00..3.01 rows=1 width=4)
> Index Cond: (price_detail_id = $0)
>
> Seq Scan on price_details (cost=0.00..2663.45 rows=4348 width=508)
> Filter: (private = false)
>(12 rows)
>----------------------------------------------------------------------------------------
>
>So it looks to me like the postgres is checking this table against
>the table that references it, and the table that reference that
>table, making what should be a very easy transaction into something
>unusable. Is there any way to avoid this without losing proper referencing?
>
>Any suggestions would be appreciated.
>
>-Scott
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: don't forget to increase your free space map settings
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: wsjordan@brownpapertickets.com
Subject: Re: Resolved - Referencing tables are grinding UPDATE to
In-Reply-To: <7.0.1.0.2.20070115145412.0545bb68@brownpapertickets.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