-- ОЧИСТКА ТАБЛИЦЫ log_write ОТ ДУБЛИРУЮЩИХ СТРОК

CREATE or replace FUNCTION f_delete_notmax() RETURNS integer
    LANGUAGE plpgsql
    AS $$
DECLARE
_rec1 record  ;
_rec2 record  ;
_rec_id integer := 0  ;
_max_id integer := 0  ;
_table_name varchar(32) ;
BEGIN

for _rec1 in
    select distinct lw_table_name from log_write
loop
    _table_name = _rec1.lw_table_name ;
    if _table_name != 'schedules' then
        RAISE NOTICE 'table_name: %', _table_name;

        for _rec2 in
            EXECUTE 'select lw_rec_id, max(log_write.id) as max_id
            from log_write
            where lw_table_name = ' || quote_literal( _table_name ) ||
            'group by lw_rec_id'
        loop
            _rec_id = _rec2.lw_rec_id ;
            _max_id = _rec2.max_id ;

            EXECUTE 'delete from log_write
            where id<' || _max_id ||
                ' and lw_rec_id = ' || _rec_id ||
                ' and lw_table_name = ' || quote_literal( _table_name );
        end loop;
    end if;
end loop;

delete from log_write where lw_table_name='schedules' ;

return 1 ;

END;
$$ security definer ;

select * from f_delete_notmax(); -- собственно чистка log_write

\d log_write
                                      Таблица "public.log_write"
    Колонка    |             Тип             |                      Модификаторы                      
---------------+-----------------------------+--------------------------------------------------------
 id            | integer                     | NOT NULL DEFAULT nextval('log_write_id_seq'::regclass)
 lw_source_id  | integer                     | 
 lw_table_name | character varying(64)       | 
 lw_rec_id     | integer                     | 
 lw_op_code    | character varying(3)        | 
 lw_datetime   | timestamp without time zone | 
Индексы:
    "log_write_pkey" PRIMARY KEY, btree (id)
    "lw_op_code" btree (lw_op_code)
    "lw_rec_id" btree (lw_rec_id)
    "lw_source_id" btree (lw_source_id)
    "lw_table_name" btree (lw_table_name)

explain analyze select distinct lw_table_name from log_write ;

                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=249019.88..249020.07 rows=19 width=13) (actual time=8767.140..8767.154 rows=32 loops=1)
   Group Key: lw_table_name
   ->  Seq Scan on log_write  (cost=0.00..234526.10 rows=5797510 width=13) (actual time=0.083..4783.462 rows=5450990 loops=1)
 Planning time: 0.709 ms
 Execution time: 8767.513 ms
(5 строк)

deallocate all;
PREPARE foo (text) AS 
select lw_rec_id, max(log_write.id) as max_id
            from log_write
            where lw_table_name = $1
            group by lw_rec_id;
EXPLAIN ANALYZE EXECUTE foo('any_name');

                                                              QUERY PLAN                                                               
---------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=38389.38..38437.01 rows=4763 width=8) (actual time=0.161..0.161 rows=0 loops=1)
   Group Key: lw_rec_id
   ->  Index Scan using lw_table_name on log_write  (cost=0.56..38327.78 rows=12320 width=8) (actual time=0.119..0.119 rows=0 loops=1)
         Index Cond: ((lw_table_name)::text = 'any_name'::text)
 Execution time: 0.360 ms
(5 строк)