[postgis-devel] ST_CoveredBy performance question

[J Smith]

On Fri, Jan 29, 2016 at 1:10 PM, Rémi Cura <remi.cura at gmail.com> wrote:
> Hey,
> you might want to use
> http://explain.depesz.com/
> for readeable explain analyse.
>
> I don't know your test protocol,
> but timing queries is difficult due to caching, stats, etc.
>
> could you try your query with an implicit inner join ?
>
> WITH "polygon" AS (
>     SELECT the_geom::geometry
>     FROM atlas
>     WHERE id = 358437
> )
> SELECT count(*) AS agggregate
> FROM "polygon" , "listings"
> WHERE ST_CoveredBy("listings"."the_geom", "polygon"."the_geom")
>
> ON a side note, if you want this to go faster, you can break your big
> polygon into smaller ones (gridding,etc)
> Cheers,
> RémiC
>

G'day Rémi.

Same performance as before in the case of using an implicit inner join.

Here's the EXPLAIN ANALYZE output for both queries in prettier formats
on depesz and using that new PEV tool which is pretty nice. First up
is the slower query:

http://explain.depesz.com/s/49M
http://tatiyants.com/pev/#/plans/plan_1454105053623

Faster:

http://explain.depesz.com/s/Bzl
http://tatiyants.com/pev/#/plans/plan_1454105016272

The query itself isn't slow overall, so I'm not worried about breaking
it up into smaller pieces or anything, I was just curious as to why
the version using ST_AsEWKB() with a cast is over 3 times faster on my
system, all things being equal. I would have thought that both queries
would produce the exact same results, but it seems that the call to
ST_AsEWKB is optimizing something away and is helping the faster
query, despite the data itself being identical in both cases.

Cheers