Re: Performance issue with Pointcloud extension

On 06/08/2017 07:27 PM, Moreno Andreo wrote:
> Il 08/06/2017 19:10, Eric Lemoine ha scritto:
>>
>> How can such a thing happen? Thanks for any insight on what could cause
>> this.
>>
>>
> I'd try raising shared_buffers to 1 GB or something near 40% of the
> available memory

I tried to make it 4G, but it does not make a difference. My machine has
16G of RAM.

>
> If you run the query again, after getting bad results, what do you get?

Always bad results.

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.887 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: 3522.135 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: 3395.672 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: 3454.466 ms

The initial "select pc_typmod_pcid(1)" query completely screws the
connection.

"select pc_typmod_pcid(1)" is just an example of a simple query that
triggers the problem. There are many others. But it has to be a query
using the Pointcloud extension.

I have no problem if I start with the main query (my "select points
from" query of interest). And running the "select pc_typmod_pcid(1)"
query in the middle does not cause any problem. It has to be run first
on the connection to do the harm. See below.

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: 280.117 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: 210.080 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: 233.095 ms
lopocs=# select pc_typmod_pcid(1);
pc_typmod_pcid
----------------
1
(1 row)

Time: 0.686 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: 199.150 ms

--
Éric Lemoine
Oslandia