Thread: Referencing tables are grinding UPDATE to a halt

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.70rows=118759098 width=50)   Join Filter: (subplan)   ->  Seq Scan on sales
(cost=0.00..3685.27rows=54627 width=42)   ->  Materialize  (cost=2663.45..2706.93 rows=4348 width=12)         ->  Seq
Scanon 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

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