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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
