Difference in queryplan for array-contains vs unnest - Mailing list pgsql-bugs

This is the version for unnest:

SELECT *

FROM foo

LEFT JOIN LATERAL (

  SELECT 1 AS present

  FROM selectionpolies

  WHERE st_intersects(selectionpolies.geo, foo.geo) AND

        selectionpolies.text in (SELECT unnest(countries.countrycodes) AS unnest FROM countries)

  LIMIT 1

) bar ON true

WHERE bar.present IS NULL AND geo && 'BOX(-1.87756 47.05916,-1.60932 47.18086)'::box2d

Nested Loop Left Join  (cost=29.87..11662.66 rows=9 width=88)

  Output: foo.id, foo.geo, (1)

  Filter: ((1) IS NULL)

  ->  Bitmap Heap Scan on foo  (cost=29.73..1982.22 rows=1796 width=84)

        Output: foo.id, foo.geo

        Recheck Cond: (foo.geo && '01030000000100000005000000B471C45A7C0AFEBF9D9D0C8E92874740B471C45A7C0AFEBFC993A46B269747408FC70C54C6BFF9BFC993A46B269747408FC70C54C6BFF9BF9D9D0C8E92874740B471C45A7C0AFEBF9D9D0C8E92874740'::geometry)

        ->  Bitmap Index Scan on foo_gidx  (cost=0.00..29.29 rows=1796 width=0)

              Index Cond: (foo.geo && '01030000000100000005000000B471C45A7C0AFEBF9D9D0C8E92874740B471C45A7C0AFEBFC993A46B269747408FC70C54C6BFF9BFC993A46B269747408FC70C54C6BFF9BF9D9D0C8E92874740B471C45A7C0AFEBF9D9D0C8E92874740'::geometry)

  ->  Limit  (cost=0.14..5.37 rows=1 width=4)

        Output: 1

        ->  Nested Loop Semi Join  (cost=0.14..5.37 rows=1 width=4)

              Output: 1

              Join Filter: (selectionpolies.text = (unnest('{nl}'::text[])))

              ->  Index Scan using selectionpolies_gidx on public.selectionpolies  (cost=0.14..2.60 rows=1 width=8)

                    Output: selectionpolies.text, selectionpolies.geo

                    Index Cond: (selectionpolies.geo && foo.geo)

                    Filter: _st_intersects(selectionpolies.geo, foo.geo)

              ->  ProjectSet  (cost=0.00..0.52 rows=100 width=32)

                    Output: unnest('{nl}'::text[])

                    ->  Result  (cost=0.00..0.01 rows=1 width=0)

For array contains:

SELECT *

FROM foo

LEFT JOIN LATERAL (

  SELECT 1 AS present

  FROM selectionpolies

  WHERE st_intersects(selectionpolies.geo, foo.geo) AND

       array[selectionpolies.text]<@( SELECT countries.countrycodes FROM countries)

  LIMIT 1

) bar ON true

WHERE bar.present IS NULL AND geo && 'BOX(-1.87756 47.05916,-1.60932 47.18086)'::box2d

Nested Loop Left Join  (cost=29.88..6714.68 rows=9 width=88)

  Output: foo.id, foo.geo, (1)

  Filter: ((1) IS NULL)

  ->  Bitmap Heap Scan on foo  (cost=29.73..1982.22 rows=1796 width=84)

        Output: foo.id, foo.geo

        Recheck Cond: (foo.geo && '01030000000100000005000000B471C45A7C0AFEBF9D9D0C8E92874740B471C45A7C0AFEBFC993A46B269747408FC70C54C6BFF9BFC993A46B269747408FC70C54C6BFF9BF9D9D0C8E92874740B471C45A7C0AFEBF9D9D0C8E92874740'::geometry)

        ->  Bitmap Index Scan on foo_gidx  (cost=0.00..29.29 rows=1796 width=0)

              Index Cond: (foo.geo && '01030000000100000005000000B471C45A7C0AFEBF9D9D0C8E92874740B471C45A7C0AFEBFC993A46B269747408FC70C54C6BFF9BFC993A46B269747408FC70C54C6BFF9BF9D9D0C8E92874740B471C45A7C0AFEBF9D9D0C8E92874740'::geometry)

  ->  Limit  (cost=0.15..2.62 rows=1 width=4)

        Output: 1

        InitPlan 1 (returns $0)

          ->  Result  (cost=0.00..0.01 rows=1 width=32)

                Output: '{nl}'::text[]

        ->  Index Scan using selectionpolies_gidx on public.selectionpolies  (cost=0.14..2.61 rows=1 width=4)

              Output: 1

              Index Cond: (selectionpolies.geo && foo.geo)

              Filter: ((ARRAY[selectionpolies.text] <@ $0) AND _st_intersects(selectionpolies.geo, foo.geo))

So in the array-contains case, the selectionpolies are filtered on the text column, while in the unnest case that filtering is postponed to the nested loop semi join (and thus calculating the st_intersects on too much (large!) polygons) despite the planner knowing it’s only one record.

Obviously the workaround will be to use the array-contains version, but I thought I mention it anyway because this could be a serious performance dip

Paul van der Linden | MapCreator

Bogert 31-06, 5612 LX, Eindhoven, The Netherlands

Main Office: +31 40 264 5120

MapCreator.eu |