-- Table: _crgactp547
CREATE TABLE _crgactp547
(
  _recordertref bytea NOT NULL,
  _recorderrref bytea NOT NULL,
  _lineno numeric(9,0) NOT NULL,
  _apdatefrom timestamp without time zone NOT NULL,
  _apdatetill timestamp without time zone NOT NULL
)
WITH (
  OIDS=FALSE
);
ALTER TABLE _crgactp547
  OWNER TO postgres;
ALTER TABLE _crgactp547 ALTER COLUMN _recordertref SET STORAGE PLAIN;
ALTER TABLE _crgactp547 ALTER COLUMN _recorderrref SET STORAGE PLAIN;

-- Index: _crgactp547_byrecorder_rn
CREATE INDEX _crgactp547_byrecorder_rn
  ON _crgactp547
  USING btree
  (_recordertref, _recorderrref, _lineno);

DELETE FROM _CRgActP547
        WHERE EXISTS(
        SELECT 1
        FROM (SELECT 1 AS SDBL_DUMMY) SDBL_DUAL
        INNER JOIN (SELECT
        T4._RecorderTRef AS RecorderTRef,
        T4._RecorderRRef AS RecorderRRef,
        T4._LineNo AS LineNo_
        FROM _CRgActP547 T4
        INNER JOIN tt523 T5
        ON T4._RecorderTRef = T5._RecorderTRef AND T4._RecorderRRef = T5._RecorderRRef AND T4._LineNo = T5._LineNo LIMIT 100000) T3
        ON _CRgActP547._RecorderTRef = T3.RecorderTRef AND _CRgActP547._RecorderRRef = T3.RecorderRRef AND _CRgActP547._LineNo = T3.LineNo_
        WHERE _CRgActP547._RecorderTRef = T3.RecorderTRef AND _CRgActP547._RecorderRRef = T3.RecorderRRef AND _CRgActP547._LineNo = T3.LineNo_)

          Buffers: shared hit=48155645, local hit=144722
          ->  Seq Scan on _crgactp547  (cost=0.00..42985042.47 rows=18102 width=6) (actual time=96536.767..96596.056 rows=46 loops=1)
                Filter: (SubPlan 1)
                Rows Removed by Filter: 36157
                Buffers: shared hit=48155599, local hit=144722
                SubPlan 1
                  ->  Nested Loop  (cost=0.41..1187.31 rows=1 width=0) (actual time=2.667..2.667 rows=0 loops=36203)
                        Buffers: shared hit=48155193, local hit=144722
                        ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=36203)
                        ->  Subquery Scan on t3  (cost=0.41..1187.28 rows=1 width=0) (actual time=2.665..2.665 rows=0 loops=36203)
                              Filter: ((t3.recordertref = _crgactp547._recordertref) AND (t3.recorderrref = _crgactp547._recorderrref) AND (t3.lineno_ = _crgactp547._lineno))
                              Rows Removed by Filter: 46
                              Buffers: shared hit=48155193, local hit=144722
                              ->  Limit  (cost=0.41..1187.02 rows=15 width=31) (actual time=0.132..2.650 rows=46 loops=36203)
                                    Buffers: shared hit=48155193, local hit=144722
                                    ->  Nested Loop  (cost=0.41..1187.02 rows=15 width=31) (actual time=0.131..2.641 rows=46 loops=36203)
                                          Buffers: shared hit=48155193, local hit=144722
                                          ->  Seq Scan on tt523 t5  (cost=0.00..8.28 rows=428 width=27) (actual time=0.004..0.072 rows=428 loops=36203)
                                                Buffers: local hit=144722
                                          ->  Index Only Scan using _crgactp547_byrecorder_rn on _crgactp547 t4  (cost=0.41..2.74 rows=1 width=31) (actual time=0.005..0.005 rows=0 loops=15484910)
                                                Index Cond: ((_recordertref = t5._recordertref) AND (_recorderrref = t5._recorderrref) AND (_lineno = t5._lineno))
                                                Heap Fetches: 1664303
                                                Buffers: shared hit=48155193

LOG:  duration: 13496.108 ms  plan:
        Query Text: DELETE FROM _CRgActP547
        WHERE EXISTS(
        SELECT 1
        FROM (SELECT 1 AS SDBL_DUMMY) SDBL_DUAL
        INNER JOIN (SELECT
        T4._RecorderTRef AS RecorderTRef,
        T4._RecorderRRef AS RecorderRRef,
        T4._LineNo AS LineNo_
        FROM _CRgActP547 T4
        INNER JOIN tt354 T5
        LEFT OUTER JOIN tt355 T6
        ON T5._RecorderTRef = T6._RecorderTRef AND T5._RecorderRRef = T6._RecorderRRef AND T5._LineNo = T6._LineNo
        ON T4._RecorderTRef = T5._RecorderTRef AND T4._RecorderRRef = T5._RecorderRRef AND T4._LineNo = T5._LineNo AND T6._LineNo IS NULL LIMIT 100000) T3
        ON _CRgActP547._RecorderTRef = T3.RecorderTRef AND _CRgActP547._RecorderRRef = T3.RecorderRRef AND _CRgActP547._LineNo = T3.LineNo_
        WHERE _CRgActP547._RecorderTRef = T3.RecorderTRef AND _CRgActP547._RecorderRRef = T3.RecorderRRef AND _CRgActP547._LineNo = T3.LineNo_)
        Delete on _crgactp547  (cost=0.00..14333050.01 rows=18102 width=6) (actual time=13496.106..13496.106 rows=0 loops=1)
          Buffers: shared hit=6417718, local hit=36257
          ->  Seq Scan on _crgactp547  (cost=0.00..14333050.01 rows=18102 width=6) (actual time=13496.104..13496.104 rows=0 loops=1)
                Filter: (SubPlan 1)
                Rows Removed by Filter: 36256
                Buffers: shared hit=6417718, local hit=36257
                SubPlan 1
                  ->  Nested Loop  (cost=28.29..395.89 rows=1 width=0) (actual time=0.371..0.371 rows=0 loops=36256)
                        Buffers: shared hit=6417312, local hit=36257
                        ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=36256)
                        ->  Subquery Scan on t3  (cost=28.29..395.86 rows=1 width=0) (actual time=0.370..0.370 rows=0 loops=36256)
                              Filter: ((t3.recordertref = _crgactp547._recordertref) AND (t3.recorderrref = _crgactp547._recorderrref) AND (t3.lineno_ = _crgactp547._lineno))
                              Buffers: shared hit=6417312, local hit=36257
                              ->  Limit  (cost=28.29..395.63 rows=13 width=31) (actual time=0.369..0.369 rows=0 loops=36256)
                                    Buffers: shared hit=6417312, local hit=36257
                                    ->  Nested Loop  (cost=28.29..395.63 rows=13 width=31) (actual time=0.369..0.369 rows=0 loops=36256)
                                          Buffers: shared hit=6417312, local hit=36257
                                          ->  Hash Anti Join  (cost=27.88..34.84 rows=92 width=27) (actual time=0.004..0.096 rows=59 loops=36256)
                                                Hash Cond: ((t5._recordertref = t6._recordertref) AND (t5._recorderrref = t6._recorderrref) AND (t5._lineno = t6._lineno))
                                                Buffers: local hit=36257
                                                ->  Seq Scan on tt354 t5  (cost=0.00..2.05 rows=105 width=27) (actual time=0.003..0.017 rows=105 loops=36256)
                                                      Buffers: local hit=36256
                                                ->  Hash  (cost=16.50..16.50 rows=650 width=78) (actual time=0.034..0.034 rows=46 loops=1)
                                                      Buckets: 1024  Batches: 1  Memory Usage: 3kB
                                                      Buffers: local hit=1
                                                      ->  Seq Scan on tt355 t6  (cost=0.00..16.50 rows=650 width=78) (actual time=0.002..0.009 rows=46 loops=1)
                                                            Buffers: local hit=1
                                          ->  Index Only Scan using _crgactp547_byrecorder_rn on _crgactp547 t4  (cost=0.41..3.91 rows=1 width=31) (actual time=0.004..0.004 rows=0 loops=2139104)
                                                Index Cond: ((_recordertref = t5._recordertref) AND (_recorderrref = t5._recorderrref) AND (_lineno = t5._lineno))
                                                Heap Fetches: 0
                                                Buffers: shared hit=6417312