How to index/optimize sum of two columns in PostgreSQL? We have a database where one table contains serialized temporary data that needs to be kept for various times (usually between tens of minutes and two weeks). We also have a low priority background process that removes old rows from the table. The background process removes up to 1000 lines during one transaction:

delete from temporarydata
    where id in (
        select id from temporarydata
            where (created + ttl) &lt;= 1553755330 limit 1000
    )


The 1553755330 in the example is current number of seconds since UNIX epoch and created contains seconds since UNIX epoch the data was added and the ttl contains the number seconds the data should be kept alive.

Technically this does work but there are around 2M lines in the temporary data and the subselect gets pretty slow because the sum requires doing sequential scan over the table to find all matching rows. This causes extra background load on the database.

&gt; explain (analyze,verbose,timing,buffers) select id from temporarydata
        where (created + ttl) &lt;= 1553755330 limit 1000

Limit  (cost=0.00..402.34 rows=1000 width=16) (actual time=6735.811..6735.811 rows=0 loops=1)
  Output: id
  Buffers: shared hit=3068 read=230500
  -&gt;  Seq Scan on public.temporarydata  (cost=0.00..262980.99 rows=653622 width=16) (actual time=6735.809..6735.809 rows=0 loops=1)
        Output: id
        Filter: ((temporarydata.created + temporarydata.ttl) &lt;= 1553755330)
        Rows Removed by Filter: 1916405
        Buffers: shared hit=3068 read=230500
Planning time: 0.402 ms
Execution time: 6735.849 ms


I'd prefer just adding a new index that can always contain the sum of created + ttl that PostgreSQL were able to use for this query automatically. Is this possible with high performance?

(I'm considering rewriting the application code to save created and expires instead of ttl where expires is created+ttl. Then I compute logical ttl as difference of those values. I think the application does not emit heavy queries on ttl alone.)
If you have used the same TTL for every record you can avoid functional indexing by simple moving the TTL to the right side of comparison (thanks to jjanes for correction):

DELETE
  FROM temporarydata
 WHERE id IN ( SELECT id 
                 FROM temporarydata
                WHERE created &lt;= 1553755330 - ttl
                LIMIT 1000
             )


Optimizer will calculate the difference only once and then will use it against the indexed created column.



If you need the different TTL for different records you can store not the created -- ttl pair but precalculated created -- expires one



And you can use functional indexing:

CREATE INDEX expires ON temporarydata ((created+ttl));