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

On Thu, May 10, 2001 at 06:44:39PM -0400, Tom Lane wrote:
> Next question: do you still have your 7.0.* DB up? Can you get an
> EXPLAIN that shows how it did it (on the real tables)?

Tom-

Okay. I started from a clean slate, by recompiling both Pgv7.1.1 and
Pgv7.1RC1, initdb'ing each (after appropriately changing /etc/ld.so.conf,
running ldconfig, etc, etc), and restoring my real DB from a previously
created dump file. I didn't do Pgv7.0.3 b/c I think it may be unnecessary
since 7.1RC1 doesn't show this problem, while 7.1.1 does. But, if you
really think it necessary, I will repeat his using 7.0.3.

Notes:
1) As usual, the 7.1RC1 returns from the "UPDATE ... " command as fast
as I press enter. The 7.1.1 returns from the "UPDATE ... " command in
about 10 minutes.
2) The two explains are identical.
3) Both updates succeed, it is only the time difference that is the
problem
4) Running "UPDATE tplantorgan SET active='t' WHERE sampleid=100430;"
(setting the boolean to true, instead of false) is instantaneous for both
7.1RC1 and 7.1.1
5) There are 8664 and 3680 tuples in the "tplantorgan" and "tplant" tables
respectively. So this is a relatively small DB.

-Jon

The actual results:
----------------------------------
Pg v7.1RC1 (restored from 2001-05-10 db dump):

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
main_v0_8=# update tplantorgan set active='f' where sampleid=100430;
UPDATE 1

----------------------------------
Pg v7.1.1 (restored from 2001-05-10 db dump):

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
main_v0_8=# update tplantorgan set active='f' where sampleid=100430;
UPDATE 1
main_v0_8=# select active from tplantorgan where sampleid=100430;
active
--------
f
(1 row)

--

-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---
Jon Lapham
Extracta Moléculas Naturais, Rio de Janeiro, Brasil
email: lapham(at)extracta(dot)com(dot)br web: http://www.extracta.com.br/
***-*--*----*-------*------------*--------------------*---------------