-2

Considering a simple table with id-datetime field. I want to remove all records, where datetime is smaller than some constant, but keep ids which have at least one record with bigger date.

My first effort was

DELETE FROM journal
WHERE id IN (
  SELECT
        j.id
  FROM
        journal j
  WHERE NOT EXIST (SELECT * FROM journal j2 
                   WHERE j.id == j2.id AND j2.created_at >= 636742944000000000
)

Which worked poorly. Then I rewrote is as following

DELETE FROM journal
WHERE id IN (
  SELECT
        j.id
  FROM
        journal j
  GROUP BY id
  HAVING MAX(created_at) < 636742944000000000;
)

Which behaved much better.

But I still wonder what's the most performant way to do so? Maybe some vendor-specific query extensions?

1

Please try this:

DELETE FROM journal
WHERE id NOT IN (
  SELECT
        j.id
  FROM
        journal j
  WHERE j.created_at >= 636742944000000000
  ORDER BY j.created_at DESC
)

Please make sure to create the index on created_at and id.

In additional, I think id in a table should be unique/primary key.

  • Are you sure it works as intended? I don't think ORDER BY actually means anything in IN clause. – Alex Zhukovskiy Oct 14 at 13:58
  • ORDER BY will make postgres to reverse scan in index table to get the record with the max of created_at. The key point is: don't scan all records, and only scan index table. – binhgreat Oct 15 at 1:30
  • Right. That sounds quite good. Thanks, I'l take that. P.S. I wonder who still downvote the question. I don't think it's stupid or violates anything... That's weird. – Alex Zhukovskiy Oct 15 at 12:08
0

I think you already are doing it the best way.

DELETE FROM journal
 WHERE id NOT in 
  (SELECT id 
    FROM journal
    GROUP BY (id)
    HAVING MAX(created_at) >= 636742944000000000);
0

You will probably be faster with NOT EXISTS:

DELETE FROM journal
WHERE NOT EXISTS
   (SELECT 1 FROM journal AS j
    WHERE j.id = journal.id
    AND j.created_at >= 636742944000000000);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.