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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

"Alex Deucher" wrote:
> 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.

Hi. This is probably a postgresql and solaris configuration problem.
When you got the new servers, did you adjust the solaris kernel
resources to their appropriate values? Sequential scans when you have
an index are a sign that postgresql may not have enough working
memory. You may need to adjust the postgresql.conf file and use
sysctl to bump up the Solaris shmmax settings. My guess is that your
old server had those set appropriately and your new system has the
default settings hence your poor performance.

http://developer.postgresql.org/pgdo...resources.html

Let me know if it helps.

Regards,

Karen