Re: reorder table data

"vinita bansal" <sagivini@xxxxxxxxxxx> · Wed, 20 Apr 2005 13:02:51 +0000

Oh Sorry!!

I gave a wrong example for update. It includes "where clause" as well which 
esentially mean that not all the rows will be modified each time. If suppose 
I already know (assuming that I can find out which rows will bw accessed) is 
there a way to organize the table data such that updates and selects become 
fast.

We cannot make changes to the application to handle such a situation (by 
creating two different tables one for active and another for inactive data) 
wherein it will access active data mostly and access inactive data from 
other table only when required since it is very complex and changing it will 
require lots of effort.

Regards,
Vinita

From: Richard Huxton <dev@xxxxxxxxxxxx>
To: vinita bansal <sagivini@xxxxxxxxxxx>
CC: jd@xxxxxxxxxxxxxxxxx, pgsql-general@xxxxxxxxxxxxxx
Subject: Re:  reorder table data
Date: Wed, 20 Apr 2005 11:03:19 +0100

vinita bansal wrote:
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.

Well, that query will obviously update the entire table, but if you can't 
predict which rows will be changed all you can do is index the appropriate 
column(s) you select against.

> 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.

Not without some pattern to work to - if you can't say which rows will be 
accessed next, then how can your database know?

Can you provide an actual example of a query you find too slow, how long it 
takes and what it's EXPLAIN ANALYSE is?

--
  Richard Huxton
  Archonet Ltd

_________________________________________________________________

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 7: don't forget to increase your free space map settings