Re: Performance with temporary table

I see, I am having practically the same problem... utilizing
partitioning idea http://www.postgresql.org/docs/8.3/interactive/ddl-partitioning.html
by table inheritance.

I have prepared a post with some trigger and rule examples for you
http://valgogtech.blogspot.com/2008/04/table-partitioning-automation-triggers.html
. So I hope you will find it useful if you are not doing it already
yourself :-).

About the use of the temporary table, I would say, that you actually
could try to add some special row status flag colum (I use "char" for
such flags) to your partitioned tables to mark some rows as unused and
then create some conditional indexes that consider this flag for your
data operation... This would make it possible for you not to creating
temporary tables I hope...

With best regards,

-- Valentine

On Apr 10, 1:33 am, sam(dot)mahindra(dot)(dot)(dot)(at)gmail(dot)com ("samantha mahindrakar")
wrote:
> 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 <val(dot)(dot)(dot)(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
>