Re: Problem with a rule on upgrade to v7.1.1

On Thu, May 10, 2001 at 05:56:11PM -0400, Tom Lane wrote:
> Jon Lapham <lapham(at)extracta(dot)com(dot)br> 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: lapham(at)extracta(dot)com(dot)br web: http://www.extracta.com.br/
***-*--*----*-------*------------*--------------------*---------------