Hi,
There is no particular pattern but it is generally the update queries of the form "update tableName set colName='something'" that are taking a lot of time incase there is a lot of background data. Also, I would not like to change my application to access data from another schema when required. I want this to be handled at database level wherein everything in database itself is organised to make access faster.
Regards, Vinita Bansal
From: "Joshua D. Drake" <jd@xxxxxxxxxxxxxxxxx> To: Richard Huxton <dev@xxxxxxxxxxxx> CC: vinita bansal <sagivini@xxxxxxxxxxx>, pgsql-general@xxxxxxxxxxxxxx Subject: Re: reorder table data Date: Tue, 19 Apr 2005 11:25:06 -0700
Richard Huxton wrote:vinita bansal wrote:
Hi,
I have a 40GB database with a few tables containing approx 10 million rows. Most of the data in these tables is inactive and there is only a few rows which get used for our benchmark run each day. We cannot delete the inactive data since it might be required for a particular run some day while it is active data that is used mostly.
Is there some pattern to which data is being accessed. For example, in an accounts system it might be rows with paid=false.
If the above is the case you could create an expression index specifically for your clause.
You could also archive out the old information into another schema and access it when required using UNIONS.
Sincerely,
Joshua D. Drake Command Prompt, Inc.
-- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedication Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
_________________________________________________________________
Find,Compare,Buy & Sell! http://adfarm.mediaplex.com/ad/ck/4686-26272-10936-265?ck=Register Do it all on eBay!
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
