Re: Problem with a rule on upgrade to v7.1.1 - Mailing list pgsql-hackers

On Thu, May 10, 2001 at 05:56:11PM -0400, Tom Lane wrote:
> Jon Lapham <lapham@extracta.com.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
onchild  (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.64rows=8664 width=0)
 

NOTICE:  QUERY PLAN:

Seq Scan on tplantorgan  (cost=0.00..237.30 rows=1 width=103)

EXPLAIN


-- 

-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---Jon LaphamExtracta Moléculas Naturais, Rio de
Janeiro,Brasilemail: lapham@extracta.com.br      web: http://www.extracta.com.br/
 
***-*--*----*-------*------------*--------------------*---------------