More Praise for 7.4RC2 - Mailing list pgsql-general

From Reece Hart
Subject More Praise for 7.4RC2
Date
Msg-id 1068597494.28850.266.camel@tallac
Whole thread Raw
List pgsql-general
More praise for 7.4RC2:

I've installed 7.4RC2 and restored a fairly complex 20GB database (from 7.3.4) with ~75M rows in 30 tables and 4 schemas, numerous triggers and constraints, procs in plpgsql and plperl. To say that it all works great hugely underestimates the improvements. Thank you! Great work, guys!


Here's one comparison of a query that choked 7.3.4 and flies in 7.4RC2:
7.3.4=> explain select count(distinct pseq_id) from paprospect2 where pmodel_id in (select pmodel_id from pmsm_prospect2 where pmodelset_id=2) and run_id=1 and svm>11::real;
                                    QUERY PLAN
----------------------------------------------------------------------------------Aggregate  (cost=78639954.60..78639954.60 rows=1 width=4)
   ->  Seq Scan on paprospect2  (cost=0.00..78639951.41 rows=1274 width=4)
         Filter: ((run_id = 1) AND (svm > 11::real) AND (subplan))
         SubPlan
           ->  Materialize  (cost=3.02..3.02 rows=3 width=4)
                 ->  Seq Scan on pmsm_prospect2  (cost=0.00..3.02 rows=3 width=4)
                       Filter: (pmodelset_id = 2)

And with the same data and indices:
7.4RC2=# explain select count(distinct pseq_id) from paprospect2 where pmodel_id in (select pmodel_id from pmsm_prospect2 where pmodelset_id=2) and run_id=1 and svm>11::real;
                                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------- Aggregate  (cost=871.09..871.09 rows=1 width=4)
   ->  Nested Loop  (cost=3.03..870.55 rows=215 width=4)
         ->  HashAggregate  (cost=3.03..3.03 rows=1 width=4)
               ->  Seq Scan on pmsm_prospect2  (cost=0.00..3.02 rows=1 width=4)
                     Filter: (pmodelset_id = 2)
         ->  Index Scan using paprospect2_search1 on paprospect2  (cost=0.00..864.84 rows=215 width=8)
               Index Cond: ((paprospect2.pmodel_id = "outer".pmodel_id) AND (paprospect2.run_id = 1) AND (paprospect2.svm > 11::

The cost estimates give the right qualitative feel. 7.3.4 took >30 minutes for this query whereas 7.4RC2 responds essentially instantaneously.

Again, thanks pgsql-hackers. This is great!

-Reece


P.S. I don't use plpython, but I did try to install the language with Python 1.5 (as I said, I don't use python). This does work with 7.3.4 on the same machine/environment. I got:
createlang: language installation failed: ERROR:  could not load library "/apps/compbio/i686-linux-2.4/opt/postgresql-7.4RC2/lib/plpython.so": /apps/compbio/i686-linux-2.4/opt/postgresql-7.4RC2/lib/plpython.so: undefined symbol: PyDict_Copy

I strongly suspect my user error. However, it's probably worth getting some python user to ensure all's well there.

-- 
Reece Hart, Ph.D.                       rkh@gene.com, http://www.gene.com/
Genentech, Inc.                         650/225-6133 (voice), -5389 (fax)
Bioinformatics and Protein Engineering
1 DNA Way, MS-93                        http://www.in-machina.com/~reece/
South San Francisco, CA  94080-4990     reece@in-machina.com, GPG: 0x25EC91A0

pgsql-general by date:

Previous
From: Adam Haberlach
Date:
Subject: Re: RHEL
Next
From: Barry C.Hawkins
Date:
Subject: Failure of make for 7.3.4 on Mac OS 10.3 Panther