delete/recreate indexes

Поиск
Список
Период
Сортировка
От alan
Тема delete/recreate indexes
Дата
Msg-id 727c5bc3-0bab-490f-add7-6eed47627ee5@ff5g2000vbb.googlegroups.com
обсуждение исходный текст
Ответы Re: delete/recreate indexes  (Jeff Davis <pgsql@j-davis.com>)
Список pgsql-performance
Hi

I'm a postgres novice so ....

I have this fairly simple table
-------------------------------------------------
 device integer not null,
 group integer not null,
 datum timestamp without time zone not null,
 val1 numeric(7,4) not null default 0.000,
 val2 numeric(7,4) not null default 0.000
-------------------------------------------------

The device column is a foreign-key to the PK of my device table.
and I have a UNIQUE INDEX on 3 columns device, group, datum

This is just a test database and I want to keep the "datum" values
(timestamps that span approx 1 month) all bound to CURRENT_DATE().

So I thought I’d just run this once (via cron) every morning.
    BEGIN;
        DROP INDEX data_unique;
        UPDATE data SET datum = (data.datum + interval '24 hours');
        CREATE UNIQUE INDEX data_unique ON public.data USING BTREE
(device, group, datum);
    COMMIT;

But
1.    it’s taking forever and
2.    I’m seeing that my disk is filling up real fast.

Any suggestions?

Alan

В списке pgsql-performance по дате отправления:

Предыдущее
От: Greg Jaskiewicz
Дата:
Сообщение: Re: does update of column with no relation imply a relation check of other column?
Следующее
От: Elisa
Дата:
Сообщение: disused indexes and performance?