Re: Deletion Challenge

On 12/09/2015 12:24 AM, Berend Tober wrote:

Adrian Klaver wrote:

On 12/05/2015 08:08 AM, Berend Tober wrote:

/*

Deletion Challenge

I want to delete all but the most recent transaction, per person, from a
table that records a transaction history because at some point the
transaction history grows large enough to adversely effect performance,
and also becomes less relevant for retention.

...

test=> delete from cash_journal where ARRAY[click, cash_journal_id]
NOT in (select max(ARRAY[click,cash_journal_id]) from cash_journal
group by fairian_id);
DELETE 7

test=> SELECT * FROM cash_journal order by fairian_id, click,
cash_journal_id;
  click | cash_journal_id | fairian_id | debit | credit | balance
|           description
-------+-----------------+------------+-------+--------+---------+----------------------------------

    412 |               1 |          7 |     5 |        |      14 |
Sold food quantity 7 units.
     37 |               7 |          8 |     8 |        |       8 |
Ratified contract f1abd670358e03
     37 |               9 |          9 |     7 |        |       7 |
Ratified contract 1574bddb75c78a
     36 |              14 |         18 |     0 |      0 |       0 |
initial cash balance
    413 |               1 |         25 |       |    995 |       0 |
Redeemed bond 7719a1c782a1ba
(5 rows)

Nice.

The idea of a NOT IN query had occurred to me briefly, but I failed to
pursue it because at some point in the distant past I had gained the
impression that NOT IN queries were not computationally efficient.
During one round of testing I had like a million rows. I'll have to run
some EXPLAIN query testing with a larger data sample for comparison.
Thanks!

Plan B:

WITH d AS
    (SELECT * FROM
        cash_journal
    LEFT JOIN
        (SELECT
            MAX(ARRAY[click,cash_journal_id]) AS mx
        FROM
            cash_journal
        GROUP BY
            fairian_id)
        AS
            mxa
    ON
        mxa.mx=ARRAY[click, cash_journal_id]
    WHERE
        mx IS NULL)
DELETE FROM
    cash_journal
USING
    d
WHERE
    d.click = cash_journal.click
AND
    d.cash_journal_id = cash_journal.cash_journal_id;