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(at)gmail(dot)com> wrote:
> On Apr 7, 8:27 pm, sam(dot)mahindra(dot)(dot)(dot)(at)gmail(dot)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(dot)(dot)(dot)(at)postgresql(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>