DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=eaCW5jX9T8fOZKM1zaG/aZeimmoJCZwIJ0rTBJ5h/cleyzkU1YIa7AbfiWs0zfUtrM6aRXZ/g2pzvToX++iMTCfyD+OTZe0RAOkfdWeRA9APY27+MAngv2Ih99js5MxMlQEhl4wSd6iyQLRyjCARGM+/p7A0nk/EKlbYd/0S+p0=
Message-ID: <a728f9f90704020750p5bfa44eex97da4f27405f7774@mail.gmail.com>
Date: Mon, 2 Apr 2007 10:50:29 -0400
From: "Alex Deucher" <alexdeucher@gmail.com>
To: "PostgreSQL Performance" <pgsql-performance@postgresql.org>
Subject: postgres 7.4 vs. 8.x redux
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Sorry if anyone receives this twice; it didn't seem to go through the
first time.  I'll attach the query plans to another email in case they
were causing a size limit problem.  Also here's the here's the table
description:

           Table "public.t1"
 Column   |          Type          | Modifiers
-----------+------------------------+-----------
 num       | character varying(30)  | not null
 c1        | character varying(500) |
 c12       | character varying(50)  |
 c2        | date                   |
 c3        | date                   |
 c11       | character varying(20)  |
 c4        | integer                |
 c5        | integer                |
 c6        | character varying(300) |
 c7        | character varying(300) |
 c8        | date                   |
 c9        | character varying(100) |
 c10       | character varying(50)  |
 c13       | integer                |
Indexes:
   "t1_pkey" primary key, btree (num)
Check constraints:
   "t1_c13" CHECK (c13 > 0 AND c13 < 6)

---------------------------------------

I had some problems a few weeks back when I tried to rebuild my
database on a SAN volume using postgres 8.1.  The back story is as
follows:

I had a large postgres 7.4 database (about 16 GB) that was originally
on an old sun box on scsi disks.  I rebuild the database from scratch
on a new sun box on a SAN volume.  The database performed poorly, and
at the time I assumed it was due to the SAN.  Well, after building a
new server with a fast scsi RAID array and rebuilding the DB, I've
come to find that it's about as only marginally faster than the SAN
based DB.  The old 7.4 databse is still significantly faster than both
new DBs and I'm not sure why.  The databases were created from scratch
using the same table structure on each server.

Hardware:

Old server:
Sun v880 (4x1.2 Ghz CPUs, 8GB RAM, non-RAID scsi JBOD volume, postgres
7.4, SQL_ASCII DB)
Solaris 8
~45/50MBps W/R

New server (with SAN storage): sun x4100 (4x opteron cores, 8GB ram,
SAN volume, postgres 8.1, UNICODE DB)
debian etch
~65/150MBps  W/R

New server (with local scsi RAID): sun x4100 (4x opteron cores, 8GB
ram,  RAID scsi volume, postgres 8.2 tried both UNICODE and SQL_ASCII
DBs)
debian etch
~160/185 MBps W/R

Most of the queries we do are significantly slower on the new servers.
 Thinking that the UTF8 format might be slowing things down, I also
tried SQL_ASCII, but the change in performance was negligible.  I've
tweaked just about every option in the config file, but nothing seems
to make a difference.  The only thing I can see that would make a
difference is the query plans.  The old 7.4 server seems to use index
scans for just about every query we throw at it.  The new servers seem
to prefer bitmap heap scans and sequential scans.  I tried adjusting
the planner options, but no matter what I did, it seems to like the
sequential and bitmap scans.  I've analyzed and vacuumed.

Does anyone have any ideas what might be going wrong?  I suppose the
next thing to try is 7.4 on the new servers, but I'd really like to
stick to the 8.x series if possible.

I've included some sample query plans below.

Thanks,

Alex