Gauri Kanekar escribió:

> Do we need to do any special config changes or any other setting for HOT to
> work??

No. HOT is always working, if it can. You don't need to configure it.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Gauri Kanekar escribió:

> Found that the size increased gradually. Is HOT working over here ??
> Guide me if im doing something wrong.

Probably not. Try vacuuming between the updates.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

On Tue, Apr 29, 2008 at 6:29 PM, Gauri Kanekar
<meetgaurikanekar@gmail.com> wrote:
>
>
> Found that the size increased gradually. Is HOT working over here ??
> Guide me if im doing something wrong.
>

You have chosen a bad case for HOT. Since you are repeatedly updating
the same set of rows, the dead space created in the first step is the
blocks which are not touched in the subsequent updates. Is this a real
scenario or are you just testing ? If its just for testing, I would
suggest updating different sets of rows in each step and then check.

Thanks,
Pavan



--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Alvaro Herrera wrote:
> Gauri Kanekar escribió:
>
>> Do we need to do any special config changes or any other setting for HOT to
>> work??
>
> No. HOT is always working, if it can. You don't need to configure it.
>

Unless you have upgraded since you started this thread you are still
running 8.1.3.

HOT is only available in 8.3 and 8.3.1

You DO need to upgrade to get the benefits of HOT



--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

HOT doesn't seems to be working in our case.

This is "table1" structure :
id integer not null
code integer not null
crid integer not null
status character varying(1) default 'A'::character varying
delta1 bigint default 0
delta2 bigint default 0
delta3 bigint default 0
delta4 bigint default 0
tz_id integer default 0
Indexes:
"idx1" PRIMARY KEY, btree (id)
"idx2" UNIQUE, btree (code, crid)
"idx3" btree (tz_id)
"idx4" btree (status)

code as crid are foreign key.

Here delta* fields get updated through out the day. and most of the time it
may update the same row again n again.

table1 contains around 12843694 records.

Now not understanding y HOT don't work in our case.

Changed fillfactor to 80, 75,70.... but nothing seems to work.

~Gauri
On Tue, Apr 29, 2008 at 10:18 PM, Shane Ambler <pgsql@sheeky.biz> wrote:

> Alvaro Herrera wrote:
>
> > Gauri Kanekar escribió:
> >
> > Do we need to do any special config changes or any other setting for
> > > HOT to
> > > work??
> > >
> >
> > No. HOT is always working, if it can. You don't need to configure it.
> >
> >
> Unless you have upgraded since you started this thread you are still
> running 8.1.3.
>
> HOT is only available in 8.3 and 8.3.1
>
> You DO need to upgrade to get the benefits of HOT
>
>
>
>
> --
>
> Shane Ambler
> pgSQL (at) Sheeky (dot) Biz
>
> Get Sheeky @ http://Sheeky.Biz
>



--
Regards
Gauri

On Wed, Apr 30, 2008 at 10:59 AM, Gauri Kanekar
<meetgaurikanekar@gmail.com> wrote:
> HOT doesn't seems to be working in our case.
>

Can you please post output of the following query ?

SELECT relid, relname, n_tup_ins, n_tup_upd, n_tup_hot_upd, n_dead_tup
from pg_stat_user_tables WHERE relname = 'table1';


Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

relid | relname | n_tup_ins | n_tup_upd | n_tup_hot_upd | n_dead_tup
-------+----------------+-----------+-----------+---------------+------------
16461 | table1 | 0 | 8352496 | 5389 | 8351242


On Wed, Apr 30, 2008 at 11:07 AM, Pavan Deolasee <pavan.deolasee@gmail.com>
wrote:

> On Wed, Apr 30, 2008 at 10:59 AM, Gauri Kanekar
> <meetgaurikanekar@gmail.com> wrote:
> > HOT doesn't seems to be working in our case.
> >
>
> Can you please post output of the following query ?
>
> SELECT relid, relname, n_tup_ins, n_tup_upd, n_tup_hot_upd, n_dead_tup
> from pg_stat_user_tables WHERE relname = 'table1';
>
>
> Thanks,
> Pavan
>
> --
> Pavan Deolasee
> EnterpriseDB http://www.enterprisedb.com
>



--
Regards
Gauri

Heikki Linnakangas wrote:

> Did you dump and reload the table after setting the fill factor? It only
> affects newly inserted data.

VACUUM FULL or CLUSTER should do the job too, right? After all, they
recreate the table so they must take the fillfactor into account.

--
Craig Ringer

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Craig Ringer wrote:
> Heikki Linnakangas wrote:
>
>> Did you dump and reload the table after setting the fill factor? It
>> only affects newly inserted data.
>
> VACUUM FULL or CLUSTER should do the job too, right? After all, they
> recreate the table so they must take the fillfactor into account.

CLUSTER, yes. VACUUM FULL won't move tuples around just to make room for
the fillfactor.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Please keep list in the loop.

On Wed, Apr 30, 2008 at 6:45 PM, Gauri Kanekar
<meetgaurikanekar@gmail.com> wrote:
> Hi,
> We have recreated the indices with fillfactor set to 80, which has improved HOT
> a little,


Wait. Did you say, you recreated the indexes with fill factor ? That's
no help for HOT. You need to recreate the TABLEs with a fill factor.
And as Heikki pointed out, you need to dump and reload, just altering
the table won't affect the current data.


Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance