Re: Performance with temporary table

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