问如何在ecto中编写复杂的子查询作为from子句？
EN

Stack Overflow用户

提问于 2018-01-19 16:12:57

回答 1查看 1.4K关注 0票数 1

我试图在subquery之后用Ecto语法编写，如何在FROM hierarchy,行之后编写子查询，它在from子句中，但我怀疑Ecto中是否可行？我想知道是否可以使用表联接甚至横向联接来执行这样的查询，而不会造成同样的性能损失？

SELECT routes.id, routes.name
FROM routes
WHERE routes.id IN
  (SELECT DISTINCT hierarchy.parent
   FROM hierarchy,
    (SELECT DISTINCT unnest(segments.rels) AS rel
     FROM segments
     WHERE ST_Intersects(segments.geom, ST_SetSrid(ST_MakeBox2D(ST_GeomFromText('POINT(1866349.262143 6886808.978425)', -1), ST_GeomFromText('POINT(1883318.282423 6876413.542579)', -1)), 3857))) AS anon_1
   WHERE hierarchy.child = anon_1.rel)

我坚持了以下代码：

hierarchy_subquery =
  Hierarchy
  |> distinct([h], h.parent)
Route
|> select([r], r.id, r.name)
|> where([r], r.id in subquery(hierarchy_subquery))

模式：

defmodule MyApp.Hierarchy do
  use MyApp.Schema

  schema "hierarchy" do
    field :parent, :integer
    field :child, :integer
    field :deph, :integer
  end
end
defmodule MyApp.Route do
  use MyApp.Schema

  schema "routes" do
    field :name, :string
    field :intnames, :map
    field :symbol, :string
    field :country, :string
    field :network, :string
    field :level, :integer
    field :top, :boolean
    field :geom, Geo.Geometry, srid: 3857
  end
end
defmodule MyApp.Segment do
  use MyApp.Schema

  schema "segments" do
    field :ways, {:array, :integer}
    field :nodes, {:array, :integer}
    field :rels, {:array, :integer}
    field :geom, Geo.LineString, srid: 3857
  end
end

我已经测试过各种查询的性能，下面的编辑是最快的：

from r in Route,
        join: h in Hierarchy, on: r.id == h.parent,
        join: s in subquery(
          from s in Segment,
            distinct: true,
            where: fragment("ST_Intersects(?, ST_SetSrid(ST_MakeBox2D(ST_GeomFromText('POINT(1285982.015631 7217169.814674)', -1), ST_GeomFromText('POINT(2371999.313507 6454022.524275)', -1)), 3857))", s.geom),
            select: %{rel: fragment("unnest(?)", s.rels)}
        ),
        where: s.rel == h.child,
        select: {r.id, r.name}

结果：

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

与上面的查询相同，但对于段子查询，join被inner_lateral_join替换为：

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

答复中的子查询：

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

我以为inner_lateral_join会更快，但它不是。有人知道如何加速这个查询吗？

postgresql

elixir

phoenix-framework

ecto

回答 1

Stack Overflow用户

回答已采纳

发布于 2018-01-20 03:40:02

以下是我要尝试的。我还没有证实它是否有效，但它应该指向正确的方向：

segments =
  from s in Segment,
    where: fragment("ST_Intersects(?, ST_SetSrid(ST_MakeBox2D(ST_GeomFromText('POINT(1866349.262143 6886808.978425)', -1), ST_GeomFromText('POINT(1883318.282423 6876413.542579)', -1)), 3857)))", s.geom),
    distinct: true,
    select: %{rel: fragment("unnest(?)", s.rel)}

hierarchy =
  from h in Hierarchy,
    join: s in subquery(segments),
    where: h.child == s.rel,
    distinct: true,
    select: %{parent: h.parent}

routes =
  from r in Route,
    join: h in subquery(hierarchy),
    where: r.top and r.id == h.parent