1

Given I use the following table :

CREATE TABLE operations (
    asset_id varchar(255) NOT NULL,
    event_id varchar(255) NULL,
    updated_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
);

and the following Index :

CREATE INDEX idx
ON operations ( event_id);

When I run the following query

WITH ROWS AS (
SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY asset_id ORDER BY updated_at DESC) AS row_num
FROM
    operations
)
SELECT
    *
FROM
    ROWS
WHERE
    row_num = 1
    AND event_id IN ('bb87d265b3c82098f7cd6913a4eb6df2', '4780d32075328c6f6f0ebe4e7fd48662')
    AND operation != 'DELETE';

the index is not used :

Subquery Scan on rows  (cost=7159.77..7909.77 rows=1 width=598) (actual time=48.159..48.160 rows=0 loops=1)
  Filter: (((rows.event_id)::text = ANY ('{bb87d265b3c82098f7cd6913a4eb6df3,4780d32075328c6f6f0ebe4e7fd48662}'::text[])) AND ((rows.operation)::text <> 'DELETE'::text) AND (rows.row_num = 1))
  Rows Removed by Filter: 20000
  ->  WindowAgg  (cost=7159.77..7559.77 rows=20000 width=598) (actual time=38.352..46.491 rows=20000 loops=1)
        Run Condition: (row_number() OVER (?) <= 1)
        ->  Sort  (cost=7159.77..7209.77 rows=20000 width=590) (actual time=38.343..39.519 rows=20000 loops=1)
              Sort Key: operations.asset_id, operations.updated_at DESC
              Sort Method: quicksort  Memory: 2911kB
              ->  Seq Scan on operations  (cost=0.00..467.00 rows=20000 width=590) (actual time=0.012..2.993 rows=20000 loops=1)
Planning Time: 0.144 ms
Execution Time: 48.436 ms

If I remove the row_num condition, then the index is used. e.g :

WITH ROWS AS (
SELECT
    *
FROM
    operations
)
SELECT
    *
FROM
    ROWS
WHERE
    event_id IN ('bb87d265b3c82098f7cd6913a4eb6df3', '4780d32075328c6f6f0ebe4e7fd48662')
    AND operation != 'DELETE';\

Can someone explain why the first query does not use the index ?

1 Answer 1

3

Can someone explain why the first query does not use the index ?

Because limiting the result set to specific event_id values affects the calculation of ROW_NUMBER() OVER (PARTITION BY asset_id ORDER BY updated_at DESC) -- row numbering can obviously change if limited to only a subset of event_ids. As a result, the condition on event_id can only be applied to the output of the CTE, not to the underlying table.

If you want the index to be used, include the event_id condition in the CTE.

1
  • That's a super clear explanation, thank you
    – egoz
    Oct 6, 2023 at 16:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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