How do I delete a fixed number of rows with sorting in PostgreSQL?
Postgres docs recommend to use array instead of IN and subquery. This should work much faster
DELETE FROM logtable
WHERE id = any (array(SELECT id FROM logtable ORDER BY timestamp LIMIT 10));
This and some other tricks can be found here
delete from logtable where log_id in (
select log_id from logtable order by timestamp limit 10);
You could try using the ctid
:
DELETE FROM logtable
WHERE ctid IN (
SELECT ctid
FROM logtable
ORDER BY timestamp
LIMIT 10
)
The ctid
is:
The physical location of the row version within its table. Note that although the
ctid
can be used to locate the row version very quickly, a row'sctid
will change if it is updated or moved byVACUUM FULL
. Thereforectid
is useless as a long-term row identifier.
There's also oid
but that only exists if you specifically ask for it when you create the table.