On Thu, May 10, 2001 at 05:56:11PM -0400, Tom Lane wrote:
> Jon Lapham <[EMAIL PROTECTED]> writes:
> > Yesterday I upgraded my database from Pg v7.1RC1 to v7.1.1. Since this
> > upgrade, I have been having unbelievable performance problems with updates
> > to a particular table, and I've tracked the problem down to a rule within
> > that table.
>
> Uh, have you VACUUM ANALYZEd yet? Those EXPLAIN numbers look
> suspiciously like default statistics ...
>
> regards, tom lane
Nope, forgot to on the little demonstration tables I made. I tacked the
post-VACUUM ANALYZE explain results (they look much better) at the end of
this email.
However, I did run a VACUUM ANALYZE on my real database. And, just to be
sure, I just ran it again. The updates still take a very, very long time
(actually it is about 12 minutes, not an hour as I previously stated, it
just feels like an hour).
I also included the explain output for my real database (main_v0_8).
Thanks Tom!
-Jon
PS: anything else I should try?
---------------------------------
test=# vacuum analyze;
VACUUM
test=# explain update child set active='t' where
childid=2;
NOTICE: QUERY PLAN:
Result (cost=0.00..2.07 rows=3 width=10)
-> Nested Loop (cost=0.00..2.07 rows=3 width=10)
-> Seq Scan on parent (cost=0.00..1.01 rows=1 width=10)
-> Seq Scan on child (cost=0.00..1.03 rows=3 width=0)
NOTICE: QUERY PLAN:
Nested Loop (cost=0.00..2.07 rows=1 width=14)
-> Seq Scan on parent (cost=0.00..1.01 rows=1 width=10)
-> Seq Scan on child (cost=0.00..1.04 rows=1 width=4)
NOTICE: QUERY PLAN:
Seq Scan on child (cost=0.00..1.04 rows=1 width=14)
EXPLAIN
-------------------------------------------
main_v0_8=# VACUUM ANALYZE;
VACUUM
main_v0_8=# explain update tplantorgan set active='f' where
sampleid=100430;
NOTICE: QUERY PLAN:
Nested Loop (cost=0.00..2243933.76 rows=1 width=239)
-> Seq Scan on tplantorgan (cost=0.00..2243931.72 rows=1 width=4)
SubPlan
-> Aggregate (cost=258.96..258.96 rows=1 width=0)
-> Seq Scan on tplantorgan (cost=0.00..258.96 rows=1
width=0)
-> Index Scan using tplant_pkey on tplant (cost=0.00..2.03 rows=1
width=235)
NOTICE: QUERY PLAN:
Result (cost=0.00..1112558.20 rows=31883520 width=235)
-> Nested Loop (cost=0.00..1112558.20 rows=31883520 width=235)
-> Seq Scan on tplant (cost=0.00..167.80 rows=3680 width=235)
-> Seq Scan on tplantorgan (cost=0.00..215.64 rows=8664 width=0)
NOTICE: QUERY PLAN:
Seq Scan on tplantorgan (cost=0.00..237.30 rows=1 width=103)
EXPLAIN
--
-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---
Jon Lapham
Extracta Moléculas Naturais, Rio de Janeiro, Brasil
email: [EMAIL PROTECTED] web: http://www.extracta.com.br/
***-*--*----*-------*------------*--------------------*---------------
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
