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@archonet.com>
>To: vinita bansal <sagivini@hotmail.com>
>CC: jd@commandprompt.com, pgsql-general@postgresql.org
>Subject: Re: [GENERAL] 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!
