Re: Performance with temporary table

The partitions are used to separate the data according to months. I
have run a query o find bad data from each such partition. The
imputation algorithm that i use requires data from 10 previous weeks
in order to impute the data. This historical data i store in a
temporary table, the i query this data so that i can take a average of
all the historical data. Before taking average some computations are
performed. Since i need the historical data for every minute of data
that i need to impute i have to store the data in some intermediate
table. Hence the temporary table.
Now i changed the code to use a permanent table that is truncated
after one set of data is imputed.
I hope this makes sense.


Samantha

On Wed, Apr 9, 2008 at 6:44 AM, valgog <valgog@gmail.com> wrote:
> On Apr 7, 8:27 pm, sam.mahindra...@gmail.com ("samantha mahindrakar")
>  wrote:
>
>
> > Hi
>  > I have written a program that imputes(or rather corrects data) with in
>  > my database.
>  > Iam using a temporary table where in i put data from other partitoined
>  > table. I then query this table to get the desired data.But the thing
>  > is this temporary table has to be craeted for every record that i need
>  > to correct and there are thousands of such records that need to be
>  > corrected.
>  > So the program necessarily creates a temporary table evrytime it has
>  > to correct a record. However this table is dropeed after each record
>  > is corrected.
>  > The program works fine.....but it runs for a very long time....or it
>  > runs for days.
>  > Iam particularyly finding that it takes more time during this statement:
>  >
>  > NOTICE:  theQuery in createtablevolumelaneshist CREATE TEMPORARY TABLE
>  > predictiontable(lane_id, measurement_start, speed,volume,occupancy) AS
>  > SELECT lane_id, measurement_start, speed,volume,occupancy
>  > FROM samantha.lane_data_I_495 WHERE
>  > lane_id IN (1317) AND
>  > measurement_start BETWEEN '2007-11-18 09:25:00' AND 2007-11-19 01:39:06'
>  >
>  > Iam not sure if i can use a cursor to replicate the functionality of
>  > the temp table. Is the performance bad because of the creation and
>  > deletion of the temp table?
>  >
>  > Thanks
>  > Samantha
>  >
>  > --
>  > Sent via pgsql-performance mailing list (pgsql-performa...@postgresql.org)
>  > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-performance
>
>  And why do you copy data from the partition tables? Did you try to
>  manipulate data directly in the needed tables? Or you are aggregating
>  some of the data there? How the partitioning is actually designed? Do
>  you use table inheritance?
>
>  -- Valentine
>
>  --
>  Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>  To make changes to your subscription:
>  http://www.postgresql.org/mailpref/pgsql-performance
>