Performance question - Mailing list pgsql-admin

I am battling a performance issue and was wondering if someone could
help.  PostgreSQL 8.1.5, FreeBSD.

I have a very intense stored procedure which performs real time
aggregation of data.

I captured the stored procedure calls from a production system and
pumped them through psql, logging duration.
The stored procedure ran at about 30ms per stored procedure call - which
was significantly faster than the previous production system.  Based on
these results, I was extremely optimistic and migrated to the new
server.

Now that we turned up the new server, the stored procedure is taking
over 250ms.
These are the execution times as recorded by enable "all" logging.

The only difference is that when I pumped the data in the test
environment, I pumped it by reading the script via psql, whereas the
current system is calling the stored procedure via ODBC through a
100Mbit ethernet connection.

I cannot explain the difference between the 2 scenarios.  Does the
duration include the overhead of the ODBC layer?

Now, the second part that was troubling relates to the execution
planner.


Two of the tables in which data is being entered are partitioned tables.
Data is being routed to the partition by using rules.  Each partition
has a check constraint on a date range.  Constraint exclusion is on.

The first partitioned table is very simple, and the stored procedure
simply inserts a record for each test which is executed.  These are
later used by a procedure running in the background which calculates
statistical data.

My problem is with the second partitioned table.  As part of my
troubleshooting, I omitted the code from the stored procedure which
accesses this data.  In this table, I attempt to retrieve the daily
record for a specific test.  If it is not found, after aggregating data
I insert it.  If the record already exists, data is aggregated and the
record is updated.

The key is composed of a date field (monthdate) and an int4 field
(kstestsysid).
The check constraints are of the form of:

ALTER TABLE tblkssnapshot12
  ADD CONSTRAINT tblkssnapshot12_chk CHECK (monthdate >= '2006-12-01
00:00:00'::timestamp without time zone AND monthdate < '2007-01-01
00:00:00'::timestamp without time zone);

I ran a sample query which would be issued by the stored procedure with
the explain option.
The query was:

select monthdate, kstestssysid from tblkssnapshotdaily where monthdate =
'2006-12-01' and kstestssysid = 3143

The explain is as follows:

select monthdate, kstestssysid from tblkssnapshotdaily where monthdate =
'2006-12-01' and kstestssysid = 3143
Result  (cost=0.00..122.49 rows=25 width=8)
  ->  Append  (cost=0.00..122.49 rows=25 width=8)
        ->  Index Scan using tblkssnapshotdaily_idx_monthtest on
tblkssnapshotdaily  (cost=0.00..3.52 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot01_idx_monthtest on
tblkssnapshot01 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot02_idx_monthtest on
tblkssnapshot02 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot03_idx_monthtest on
tblkssnapshot03 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot04_idx_monthtest on
tblkssnapshot04 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot05_idx_monthtest on
tblkssnapshot05 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot06_idx_monthtest on
tblkssnapshot06 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot07_idx_monthtest on
tblkssnapshot07 tblkssnapshotdaily  (cost=0.00..5.94 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot08_idx_monthtest on
tblkssnapshot08 tblkssnapshotdaily  (cost=0.00..4.73 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot09_idx_monthtest on
tblkssnapshot09 tblkssnapshotdaily  (cost=0.00..5.56 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot10_idx_monthtest on
tblkssnapshot10 tblkssnapshotdaily  (cost=0.00..5.75 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot11_idx_monthtest on
tblkssnapshot11 tblkssnapshotdaily  (cost=0.00..5.81 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot12_idx_monthtest on
tblkssnapshot12 tblkssnapshotdaily  (cost=0.00..4.33 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot13_idx_monthtest on
tblkssnapshot13 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot14_idx_monthtest on
tblkssnapshot14 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot15_idx_monthtest on
tblkssnapshot15 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot16_idx_monthtest on
tblkssnapshot16 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot17_idx_monthtest on
tblkssnapshot17 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot18_idx_monthtest on
tblkssnapshot18 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot19_idx_monthtest on
tblkssnapshot19 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot20_idx_monthtest on
tblkssnapshot20 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot21_idx_monthtest on
tblkssnapshot21 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot22_idx_monthtest on
tblkssnapshot22 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot23_idx_monthtest on
tblkssnapshot23 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
        ->  Index Scan using tblkssnapshot24_idx_monthtest on
tblkssnapshot24 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
              Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))

If constraint exclusion is on, why are index scans taking place on all
of the tables and not only on the only partitioned table for which the
constraint exists?

I went to a partitioned table thinking this would improve performance
from the previous schema in which all of the data was in the parent
table, but the end result was significantly slower performance (by
orders of magnitude).