我试图提高在Postgres 9.4数据库上SQL查询的性能。我设法重写了查询,以便使用索引,并且现在速度非常快!但是我不太理解为什么。
这是原始查询:
SELECT DISTINCT dt.id, dt.updated_at
FROM public.day dt
INNER JOIN public.optimized_localized_day sldt ON sldt.day_id = dt.id
INNER JOIN public.day_template_place dtp ON dtp.day_template_id = dt.id
INNER JOIN public.optimized_place op ON op.geoname_id = dtp.geoname_id
WHERE
op.alternate_localized_names ILIKE unaccent('%query%') OR
lower(sldt.unaccent_title) LIKE unaccent(lower('%query%')) OR
lower(sldt.unaccent_description) LIKE unaccent(lower('%query%'))
ORDER BY dt.updated_at DESC
LIMIT 100;
我已经在op.alternate_localized_names
、lower(sldt.unaccent_title)
和lower(sldt.unaccent_description)
上使用了pg_trgm来放置三个三元组索引。
但是,Postgres没有使用它们,而是执行了一次SeqScan来加入整张表格,如EXPLAIN
所示:
Limit
-> Unique
-> Sort
Sort Key: dt.updated_at, dt.id
-> Hash Join
Hash Cond: (sldt.day_id = dt.id)
Join Filter: ((op.alternate_localized_names ~~* unaccent('%query%'::text)) OR (lower(sldt.unaccent_title) ~~ unaccent('%query%'::text)) OR (lower(sldt.unaccent_description) ~~ unaccent('%query%'::text)))
-> Seq Scan on optimized_localized_day sldt
-> Hash
-> Hash Join
Hash Cond: (dtp.geoname_id = op.geoname_id)
-> Hash Join
Hash Cond: (dtp.day_template_id = dt.id)
-> Seq Scan on day_template_place dtp
-> Hash
-> Seq Scan on day dt
-> Hash
-> Seq Scan on optimized_place op
然而,当我将查询拆分成两部分,一部分搜索 public.optimized_localized_day
,另一部分搜索 public.optimized_place
时,它现在使用它们的索引:
SELECT DISTINCT dt.id, dt.updated_at
FROM public.day dt
INNER JOIN public.day_template_place dtp ON dtp.day_template_id = dt.id
INNER JOIN public.optimized_place op ON op.geoname_id = dtp.geoname_id
WHERE op.alternate_localized_names ILIKE unaccent('%query%')
UNION
SELECT DISTINCT dt.id, dt.updated_at
FROM public.day dt
INNER JOIN public.optimized_localized_day sldt ON sldt.day_id = dt.id
WHERE lower(sldt.unaccent_title) LIKE unaccent(lower('%query%'))
OR lower(sldt.unaccent_description) LIKE unaccent(lower('%query%'));
关于 EXPLAIN
:
HashAggregate
-> Append
-> HashAggregate
-> Nested Loop
-> Nested Loop
-> Bitmap Heap Scan on optimized_place op
Recheck Cond: (alternate_localized_names ~~* unaccent('%query%'::text))
-> Bitmap Index Scan on idx_trgm_place_lower
Index Cond: (alternate_localized_names ~~* unaccent('%jericho%'::text))
-> Bitmap Heap Scan on day_template_place dtp
Recheck Cond: (geoname_id = op.geoname_id)
-> Bitmap Index Scan on day_template_place_geoname_idx
Index Cond: (geoname_id = op.geoname_id)
-> Index Scan using day_pkey on day dt
Index Cond: (id = dtp.day_template_id)
-> HashAggregate
-> Nested Loop
-> Bitmap Heap Scan on optimized_localized_day sldt
Recheck Cond: ((lower(unaccent_title) ~~ unaccent('%query%'::text)) OR (lower(unaccent_description) ~~ unaccent('%query%'::text)))
-> BitmapOr
-> Bitmap Index Scan on tgrm_idx_localized_day_title
Index Cond: (lower(unaccent_title) ~~ unaccent('%query%'::text))
-> Bitmap Index Scan on tgrm_idx_localized_day_description
Index Cond: (lower(unaccent_description) ~~ unaccent('%query%'::text))
-> Index Scan using day_pkey on day dt_1
Index Cond: (id = sldt.day_id)
据我所知,在
OR
子句中在两个不同的表上设置条件会导致Postgres先连接这些表,然后再进行筛选。但是我对此并不确定。还有一件让我困惑的事情是,我想了解Postgres如何管理第二个查询中的筛选。你们知道Postgres如何处理这两种情况吗? 谢谢 :)
LIKE
过滤器在表连接之前被应用,因此Postgres在连接后需要连接的行数更少,我理解正确吗? - RomWW12LIKE
条件是使用~~
运算符的条件。它们在问题的计划中位于连接之前。只有在外部连接时,将条件编写为连接条件或WHERE
条件才会有影响。 - Laurenz Albe