代码之家 › 专栏 › 技术社区 › luzny

如何使用子查询优化sql查询,可能是通过横向连接?

lateral-join postgis optimization postgresql sql

luzny · 技术社区 · 7 年前

我正在尝试优化复杂的sql查询,它将在每次映射绑定框更改时执行。我以为 INNER LATERAL JOIN 将是最快的,但它不是。有人知道如何加速此查询以及如何更好地利用 LATERAL JOIN ?

我最快的查询:

SELECT r0."id", r0."name" 
FROM "hiking"."routes" AS r0 
INNER JOIN "hiking"."hierarchy" AS h1 ON r0."id" = h1."parent" 
INNER JOIN (SELECT DISTINCT unnest(s0."rels") AS "rel" 
            FROM "hiking"."segments" AS s0 
            WHERE (ST_Intersects(s0."geom", ST_SetSrid(ST_MakeBox2D(ST_GeomFromText('POINT(1285982.015631 7217169.814674)', -1), ST_GeomFromText('POINT(2371999.313507 6454022.524275)', -1)), 3857)))) AS s2 ON TRUE 
WHERE (s2."rel" = h1."child");

计划时间:~ 0.605 ms执行时间:~ 37.232 ms

实际上与上述相同,但 横向连接 ,速度较慢是否正确?

SELECT r0."id", r0."name" 
FROM "hiking"."routes" AS r0 
INNER JOIN "hiking"."hierarchy" AS h1 ON r0."id" = h1."parent" 
INNER JOIN LATERAL (SELECT DISTINCT unnest(s0."rels") AS "rel" 
                    FROM "hiking"."segments" AS s0 
                    WHERE (ST_Intersects(s0."geom", ST_SetSrid(ST_MakeBox2D(ST_GeomFromText('POINT(1285982.015631 7217169.814674)', -1), ST_GeomFromText('POINT(2371999.313507 6454022.524275)', -1)), 3857)))) AS s2 ON TRUE 
WHERE (s2."rel" = h1."child");

计划时间:~ 1.353 ms执行时间:~ 38.518 ms

子查询中具有子查询的最慢查询(这是我的第一次,所以我对其进行了一些改进):

SELECT r0."id", r0."name" 
FROM "hiking"."routes" AS r0 
INNER JOIN (SELECT DISTINCT h0."parent" AS "parent" 
            FROM "hiking"."hierarchy" AS h0 
            INNER JOIN (SELECT DISTINCT unnest(s0."rels") AS "rel" 
                        FROM "hiking"."segments" AS s0 
                        WHERE (ST_Intersects(s0."geom", ST_SetSrid(ST_MakeBox2D(ST_GeomFromText('POINT(1285982.015631 7217169.814674)', -1), ST_GeomFromText('POINT(2371999.313507 6454022.524275)', -1)), 3857)))) AS s1 ON TRUE 
            WHERE (h0."child" = s1."rel")) AS s1 ON TRUE 
WHERE (r0."top" AND (r0."id" = s1."parent"));

计划时间:~ 1.017 ms执行时间:~ 41.288 ms

1 回复 | 直到 7 年前

Abelisto 7 年前

在不了解数据库的情况下,很难再现查询的逻辑,但我会尝试,所以要有耐心:

SELECT r0."id", r0."name" 
FROM "hiking"."routes" AS r0 
INNER JOIN "hiking"."hierarchy" AS h1 ON r0."id" = h1."parent" 
WHERE 
  EXISTS (
    SELECT 1
    FROM "hiking"."segments" AS s0 
    WHERE (
      ST_Intersects(
        s0."geom",
        ST_SetSrid(ST_MakeBox2D(ST_GeomFromText('POINT(1285982.015631 7217169.814674)', -1), ST_GeomFromText('POINT(2371999.313507 6454022.524275)', -1)),
        3857)))
      AND array[h1."child"] <@ s0."rels");

有两点:

按筛选数据 EXISTS 或 NOT EXISTS 有时通过加入
可以使用数组比较运算符,而不是取消对数组字段的测试以将其元素与某个值进行比较。有了适当的GIN索引,速度要快得多(文档 here 和 here ).

下面是一个简单的示例,说明如何在数组上使用索引以及如何更快地使用索引:

create table foo(bar int[]);
insert into foo(bar) select array[1,2,3,x] from generate_series(1,1000000) as x;
create index idx on foo using gin (bar); // Note this
select * from foo where 666 in (select unnest(bar)); // 6936,345 ms on my HW
select * from foo where array[666] <@ bar; // 45,524 ms