|
Thanks Benjamin The issue I have with my situation is tht
the target table typically has millions of records (not unusual to have tens of
millions) and the update is based on 80% of the columns. The update is a
killer w.r.t performance. I plan to create an index on all columns and
test the individual update. Regards Sriram From: Benjamin
Krajmalnik [mailto:kraj@xxxxxxxxxxx] Siriam, I have no clue what it is you are trying to do, but I have a
similar situation. I have a monitoring system which sends data to our
psotgresql database. The system calls a single stored procedure which
performs on-the-fly data aggregation for the particular test for which the data
is being sent. You may want to look at some numerical methods books and
see if you can do what I am doing. I keep a record with computed values
representing the data up to the previous transaction, and recompute
accordingly. This is done estremely quickly. Then, twice a day, I
run a stored procedure which runs some other aggregations based on the raw data
which was sent over. It was taking about 30 seconds against a table with
about 500K records, aggregating to a table with 2K records. Since not all
of the tests need this particular data aggregation, an additional field was
added to the test descriptor to flag those tests which need to have the particular
aggregation (essentially, computing statistical process control data).
After doing this, our 30 seconds went down to about 2 seconds. I do not have access to our aggregation dunction, but I am
using one cursor and 2 record types. First, I perform a loop on a selection into the record
structure for the candidate tests. Within this loop, I create a cursor
which fetches the row data into another record structure. This record
structure now holds the data we need in order to update the table on which the
primary loop is running. I do not know if this is the optimum way of doing this
within PostgreSQL, but the perofrmance appears to be fine. I have
not goten into the fine tuning for speed just yet. Just an idea - hope it helps. From:
pgsql-admin-owner@xxxxxxxxxxxxxx on behalf of Sriram Dandapani Yes..all
of it is in one transaction as there is a window of record ids |
