-- ОЧИСТКА ТАБЛИЦЫ 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 строк)

                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Delete on log_write  (cost=9.15..17.18 rows=1 width=6) (actual time=0.054..0.054 rows=0 loops=1)
   ->  Index Scan using lw_table_name on log_write  (cost=9.15..17.18 rows=1 width=6) (actual time=0.050..0.050 rows=0 loops=1)
         Index Cond: ((lw_table_name)::text = 'any_lw_table_name'::text)
         Filter: (NOT (hashed SubPlan 1))
         SubPlan 1
           ->  HashAggregate  (cost=8.58..8.59 rows=1 width=21) (never executed)
                 Group Key: log_write_1.lw_table_name, log_write_1.lw_rec_id
                 ->  Index Scan using lw_table_name on log_write log_write_1  (cost=0.56..8.57 rows=1 width=21) (never executed)
                       Index Cond: ((lw_table_name)::text = 'any_lw_table_name'::text)
 Planning time: 0.407 ms
 Execution time: 0.185 ms
(11 строк)

                                                                              QUERY PLAN                                                                               
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Delete on log_write  (cost=45099.01..90120.78 rows=7361 width=6) (actual time=289.060..289.060 rows=0 loops=1)
   ->  Index Scan using lw_table_name on log_write  (cost=45099.01..90120.78 rows=7361 width=6) (actual time=289.057..289.057 rows=0 loops=1)
         Index Cond: ((lw_table_name)::text = 'students'::text)
         Filter: (NOT (hashed SubPlan 1))
         Rows Removed by Filter: 29613
         SubPlan 1
           ->  HashAggregate  (cost=45022.32..45083.23 rows=6091 width=21) (actual time=241.065..251.146 rows=29613 loops=1)
                 Group Key: log_write_1.lw_table_name, log_write_1.lw_rec_id
                 ->  Index Scan using lw_table_name on log_write log_write_1  (cost=0.56..44911.90 rows=14722 width=21) (actual time=0.087..213.062 rows=29613 loops=1)
                       Index Cond: ((lw_table_name)::text = 'students'::text)
 Planning time: 3.927 ms
 Execution time: 290.153 ms
(12 строк)

                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Delete on log_write  (cost=45099.01..90120.78 rows=7361 width=6)
   ->  Index Scan using lw_table_name on log_write  (cost=45099.01..90120.78 rows=7361 width=6)
         Index Cond: ((lw_table_name)::text = 'students'::text)
         Filter: (NOT (hashed SubPlan 1))
         SubPlan 1
           ->  HashAggregate  (cost=45022.32..45083.23 rows=6091 width=21)
                 Group Key: log_write_1.lw_table_name, log_write_1.lw_rec_id
                 ->  Index Scan using lw_table_name on log_write log_write_1  (cost=0.56..44911.90 rows=14722 width=21)
                       Index Cond: ((lw_table_name)::text = 'students'::text)
(9 строк)

-- teachers_marks_link связь препод-оценка

                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Delete on log_write  (cost=45098.46..328247.26 rows=1349795 width=6)
   ->  Seq Scan on log_write  (cost=45098.46..328247.26 rows=1349795 width=6)
         Filter: (((lw_table_name)::text = 'teachers_marks_link'::text) AND (NOT (hashed SubPlan 1)))
         SubPlan 1
           ->  HashAggregate  (cost=45022.32..45083.23 rows=6091 width=21)
                 Group Key: log_write_1.lw_table_name, log_write_1.lw_rec_id
                 ->  Index Scan using lw_table_name on log_write log_write_1  (cost=0.56..44911.90 rows=14722 width=21)
                       Index Cond: ((lw_table_name)::text = 'students'::text)
(8 строк)

-- без таблицы
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Delete on log_write  (cost=45098.46..314922.53 rows=2664945 width=6)
   ->  Seq Scan on log_write  (cost=45098.46..314922.53 rows=2664945 width=6)
         Filter: (NOT (hashed SubPlan 1))
         SubPlan 1
           ->  HashAggregate  (cost=45022.32..45083.23 rows=6091 width=21)
                 Group Key: log_write_1.lw_table_name, log_write_1.lw_rec_id
                 ->  Index Scan using lw_table_name on log_write log_write_1  (cost=0.56..44911.90 rows=14722 width=21)
                       Index Cond: ((lw_table_name)::text = 'students'::text)
(8 строк)