鉴于我使用下表:
CREATE TABLE operations (
asset_id varchar(255) NOT NULL,
event_id varchar(255) NULL,
updated_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
);
以及以下索引:
CREATE INDEX idx
ON operations ( event_id);
当我运行以下查询时
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';
未使用索引:
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
如果我删除 row_num 条件,则使用索引。例如:
WITH ROWS AS (
SELECT
*
FROM
operations
)
SELECT
*
FROM
ROWS
WHERE
event_id IN ('bb87d265b3c82098f7cd6913a4eb6df3', '4780d32075328c6f6f0ebe4e7fd48662')
AND operation != 'DELETE';\
有人可以解释为什么第一个查询不使用索引吗?