Re: Deletion Challenge

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])
fromcash_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!