此查询需要2秒钟才能完成：
explain analyze select
*
from
"MdChapters" as "MdChapter"
where
("MdChapter"."countryCode" = 'gb' AND "MdChapter"."deletedAt" is null)
order by
"MdChapter"."id" desc
limit 100;
Limit (cost=56.35..56.38 rows=13 width=2656) (actual time=1854.038..1854.409 rows=100 loops=1)
-> Sort (cost=56.35..56.38 rows=13 width=2656) (actual time=1854.035..1854.163 rows=100 loops=1)
Sort Key: id DESC
Sort Method: top-N heapsort Memory: 150kB
-> Bitmap Heap Scan on "MdChapters" "MdChapter" (cost=4.56..56.10 rows=13 width=2656) (actual time=49.818..1355.082 rows=327179 loops=1)
Recheck Cond: ((("countryCode")::text = 'gb'::text) AND ("deletedAt" IS NULL))
Heap Blocks: exact=47298
-> Bitmap Index Scan on test (cost=0.00..4.55 rows=13 width=0) (actual time=42.630..42.632 rows=328948 loops=1)
Index Cond: ((("countryCode")::text = 'gb'::text) AND ("deletedAt" IS NULL))
Planning time: 0.200 ms
Execution time: 1854.567 ms
但是此查询只需<0.1s：
select
*
from
"MdChapters" as "MdChapter"
where
("MdChapter"."countryCode" = 'gb')
order by
"MdChapter"."id" desc
limit 100;
我在columcountryCode和deletedAt
CREATE INDEX md_chapters_deleted_at_country_code ON public."MdChapters" USING btree ("deletedAt", "countryCode");
我会创建过滤/局部索引：
CREATE INDEX md_chapters_deleted_at_country_code2 ON public."MdChapters"
USING btree ("countryCode", "id")
WHERE ("deletedAt" IS NULL);
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句