More Praise for 7.4RC2

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(at)gene(dot)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(at)in-machina(dot)com, GPG: 0x25EC91A0