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:
And with the same data and indices:
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.
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: