From: Alex Deucher <alexdeucher@gmail.com>
To: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: postgres 7.4 vs. 8.x redux
Date: Mon, 2 Apr 2007 10:50:29 -0400
Message-ID: <a728f9f90704020750p5bfa44eex97da4f27405f7774@mail.gmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: alexdeucher@gmail.com
Subject: Re: postgres 7.4 vs. 8.x redux
In-Reply-To: <a728f9f90704020750p5bfa44eex97da4f27405f7774@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox