two queries and dual cpu (perplexed) - Mailing list pgsql-performance

Hi everybody,

One of our clients was using SQL-Server and decided to switch to
PostgreSQL 8.0.1.

Hardware: Dual processor  Intel(R) Xeon(TM) CPU 3.40GHz
OS: Enterprise Linux with 2.6.9-5 SMP kernel
Filesystem: ext3
SHMMAX: $ cat  /proc/sys/kernel/shmmax
6442450944 <--- beleive that's ~6.5 GB, total ram is 8GB
Database: 15GB in size with a few tables with over 80 million rows.

Here is a snippit from the output of
SELECT oid , relname, relpages, reltuples
        FROM pg_class ORDER BY relpages DESC;
    oid    |             relname             | relpages |  reltuples
-----------+---------------------------------+----------+-------------
     16996 | CurrentAusClimate               |   474551 | 8.06736e+07
     16983 | ClimateChangeModel40            |   338252 | 5.31055e+07
 157821816 | PK_CurrentAusClimate            |   265628 | 8.06736e+07
 157835995 | idx_climateid                   |   176645 | 8.06736e+07
 157835996 | idx_ausposnum                   |   176645 | 8.06736e+07
 157835997 | idx_climatevalue              |   176645 | 8.06736e+07
 157821808 | PK_ClimateModelChange_40        |   174858 | 5.31055e+07
 157821788 | IX_iMonth001                    |   116280 | 5.31055e+07
 157821787 | IX_ClimateId                    |   116280 | 5.31055e+07
 157821786 | IX_AusPosNumber                 |   116280 | 5.31055e+07
     17034 | NeighbourhoodTable              |    54312 | 1.00476e+07
 157821854 | PK_NeighbourhoodTable           |    27552 | 1.00476e+07
 157821801 | IX_NeighbourhoodId              |    22002 | 1.00476e+07
 157821800 | IX_NAusPosNumber                |    22002 | 1.00476e+07
 157821799 | IX_AusPosNumber006              |    22002 | 1.00476e+07
[...]

To test the performance of the database we ran one of the most demanding
queries that exist with the following embarrassing results:

Query Execution time on:
SQL-Server (dual processor xeon)  3min 11sec
PostgreSQL (SMP IBM Linux server) 5min 30sec

Now I have not touch the $PGDATA/postgresql.conf (As I know very little
about memory tuning) Have run VACCUM & ANALYZE.

The client understands that they may not match the performance for a
single query as there is no multithreading. So they asked me to
demonstrate the benefits of Postgresql's multiprocessing capabilities.

To do that I modified the most demanding query to create a second query
and ran them in parallel:

$ time ./run_test1.sh
$ cat ./run_test1.sh
/usr/bin/time -p psql -f ./q1.sql ausclimate > q1.out 2>q1.time &
/usr/bin/time -p psql -f ./q2.sql ausclimate > q2.out 2>q2.time

and the time taken is *twice* that for the original. The modification was
minor. The queries do make use of both CPUs:

 2388 postgres 16 0 79640 15m 11m R 80.9 0.2 5:05.81 postmaster
 2389 postgres 16 0 79640 15m 11m R 66.2 0.2 5:04.25 postmaster

But I can't understand why there's no performance improvement and infact
there seems to be no benefit of multiprocessing.  Any ideas? I don't know
enough about the locking procedures employed by postgres but one would
think this shouldn't be and issue with read-only queries.

Please don't hesitate to ask me for more info like, the query or the
output of explain, or stats on memory usage. I just wanted to keep this
short and provide more info as the cogs start turning :-)

Thanks & Regards
Shoaib