[postgis-users] Query plan improvement when identical st_intersects filter is repeated twice

[Sandro Santilli]

On Thu, May 21, 2015 at 12:53:00AM +0000, Trang Nguyen wrote:
> Hi,
> 
> I am tuning query performance on the  following query and am seeing two completely different query plans for the following. The only difference is that the second query repeats the same filter “st_intersects(waypoints, zone_geom)” a second time, which somehow  seems to trigger an index scan on the gist indexed LINESTRING column “waypoints” that doesn’t occur in the first query.

I guess it depends on the cost of the ST_Intersects function, which is
likely run twice when specified twice. Someone explained it to me but I
keep not getting why an immutable function should be ever called twice...

You might want to report this to PostgreSQL to try at understanding the
gory details.

--strk;