Re: Performance issue with Pointcloud extension

On 06/08/2017 09:00 AM, Eric Lemoine wrote:
> Hi
>
> We have a rather strange performance issue with the Pointcloud extension
> [*]. The issue/bug may be in the extension, but we don't know for sure
> at this point. I'm writing to the list to hopefully get some guidance on
> how to further debug this.
>
> [*] <https://github.com/pgpointcloud/pointcloud>
>
> A query takes around 250 ms when executed first on a database
> connection. But it takes like 3 s when executed after a first very
> simple Pointcloud query.
>
> Below is a test-case with psql.
>
> Case #1 (works normally):
>
> psql (9.6.3)
> Type "help" for help.
>
> lopocs=# \timing
> Timing is on.
> lopocs=# select points from public.sthelens where pc_intersects(points,
> st_geomfromtext('polygon ((-2357334.41980829 -3742654.00016992,
> -2356120.91980829 -3742654.00016992, -2356120.91980829
> -3741278.00016992, -2357334.41980829 -3741278.00016992,
> -2357334.41980829 -3742654.00016992))', 4978));
> Time: 236.423 ms
>
>
> Case #2 (works abnormally):
>
> psql (9.6.3)
> Type "help" for help.
>
> lopocs=# \timing
> Timing is on.
> lopocs=# select pc_typmod_pcid(1);
> pc_typmod_pcid
> ----------------
> 1
> (1 row)
>
> Time: 4.917 ms
> lopocs=# select points from public.sthelens where pc_intersects(points,
> st_geomfromtext('polygon ((-2357334.41980829 -3742654.00016992,
> -2356120.91980829 -3742654.00016992, -2356120.91980829
> -3741278.00016992, -2357334.41980829 -3741278.00016992,
> -2357334.41980829 -3742654.00016992))', 4978));
> Time: 2987.491 ms
>
>
> The query takes 236 ms in case #1, and 2987 ms in case #2! Huge difference.
>
> Anyone has any idea where this performance drop may come from? The
> problem may be in the Pointcloud in the extension, but I have no idea
> where the bug may be.
>
> Any idea? Any suggestion on how to debug this? This has been driving us
> crazy for some time now.

Looks like you also have postgis and pointcloud_postgis in mix. I would
say this may get an answer sooner here:

http://lists.osgeo.org/mailman/listinfo/pgpointcloud/

>
> Thanks.
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com