DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=FHo2rb6XdfGHmVXz4HmDR9cGp53EYFb5TyhGXFgATAONOOxzWSIkyMTnpTSx4hbY82UbiuJFWlScqzIDkIId1Bl17qHYRQYiVjKpul0Ym8wM4PXEFbEBSL/GGNMSk8nuLO+v/YdnMmcWBLHY0/1Vsl27N/0eEGm9WYce+lAh/XU=
Message-ID: <f0c828c40804091633n2ded79e1l853098f02041a951@mail.gmail.com>
Date: Wed, 9 Apr 2008 19:33:46 -0400
From: "samantha mahindrakar" <sam.mahindrakar@gmail.com>
To: valgog <valgog@gmail.com>
Subject: Re: Performance with temporary table
Cc: pgsql-performance@postgresql.org
In-Reply-To: 
 <16cbbce7-9775-4ee5-8eb1-fdf56b7b8a3c@x19g2000prg.googlegroups.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <f0c828c40804071127u512a5f89ka7866a148e3c7c21@mail.gmail.com>
	<16cbbce7-9775-4ee5-8eb1-fdf56b7b8a3c@x19g2000prg.googlegroups.com>

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
>