Re: Performance question - Mailing list pgsql-admin

Are you sure you're doing an apples-apples comparison? Is the load on
both machines the same, or does production have extra stuff running?
Have you tried your original test again in the same setup without
ODBC to eliminate that possibility? (or redone your original test
with ODBC).

As for the constraint exclusion, try changing the query so that
'2006-12-01' is cast to a timestamp (or change the constraints so
that you're constraining dates... the check constraints should really
match the type of the underlying table).

On Dec 1, 2006, at 9:18 PM, Benjamin Krajmalnik wrote:

> 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).
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)