[GENERAL] Performance issue with Pointcloud extension

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.

Thanks.


--
Éric Lemoine
Oslandia