From: samantha mahindrakar <sam.mahindrakar@gmail.com>
To: valgog <valgog@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance with temporary table
Date: Wed, 9 Apr 2008 19:33:46 -0400
Message-ID: <f0c828c40804091633n2ded79e1l853098f02041a951@mail.gmail.com> (raw)
In-Reply-To: <16cbbce7-9775-4ee5-8eb1-fdf56b7b8a3c@x19g2000prg.googlegroups.com>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sam.mahindrakar@gmail.com, valgog@gmail.com
Subject: Re: Performance with temporary table
In-Reply-To: <f0c828c40804091633n2ded79e1l853098f02041a951@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox