时间戳

首页 > 解决方案 > 索引不适用于 postgres 中的日期列。Top-N-heapsort 不应用索引扫描

sql - 索引不适用于 postgres 中的日期列。Top-N-heapsort 不应用索引扫描

问题描述

我有一个表“帖子”，它有数百万行。我需要使用日期字段的前 N ​​条记录，但由于 Top-N 堆排序，它花费了太多时间。如何优化此查询？

注意：在我的数据较少的登台服务器上，它工作正常。索引扫描正在那里应用。

数据库版本：Postgres 12

    CREATE TABLE public.posts
(
    post_id integer NOT NULL DEFAULT (primary key),
    team_id integer NOT NULL,
    is_active boolean DEFAULT true,
    "createdAt" timestamp with time zone,
    "updatedAt" timestamp with time zone,
)

索引列是：

"createdAt DESC NULLS Last",
(team_id ASC NULLS LAST, "createdAt" DESC NULLS LAST),
team_id ASC NULLS LAST

询问：

SELECT p.*
FROM posts p
WHERE team_id = 1
AND p.is_active = true AND "createdAt" IS NOT NULL
ORDER BY "createdAt" DESC 
LIMIT 20;

查询计划：

 "Limit  (cost=138958.67..138958.72 rows=20 width=360) (actual time=356.391..356.419 rows=20 loops=1)"
"  ->  Sort  (cost=138958.67..139078.57 rows=47960 width=360) (actual time=356.389..356.402 rows=20 loops=1)"
"        Sort Key: ""createdAt"" DESC"
"        Sort Method: top-N heapsort  Memory: 34kB"
"        ->  Index Scan using posts_team_id on posts p  (cost=0.44..137682.47 rows=47960 width=360) (actual time=0.042..317.258 rows=52858 loops=1)"
"              Index Cond: (team_id = 1)"
"              Filter: (is_active AND (""createdAt"" IS NOT NULL))"
"Planning Time: 0.145 ms"
"Execution Time: 356.459 ms"

标签： sqlpostgresqlindexingheapsortpostgresql-12

解决方案

这是我将使用的索引：

CREATE INDEX ON posts (team_id, "createdAt") WHERE is_active;

它支持WHERE条件和ORDER BY.

推荐阅读