Re: [GENERAL] Performance issue with Pointcloud extension - Mailing list pgsql-general

On Thu, 8 Jun 2017 18:00:04 +0200
Eric Lemoine <eric.lemoine@oslandia.com> wrote:

> 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.

Lots of missing information here ...

Is there an index on public.sthelens.points? How many rows are in that table?
What are your shared_buffers settings? How much RAM does the server have?
What does EXPLAIN look like for that query? How large (in bytes) are the
tables in question? What does pc_typmod_pcid() actually do?

There are probably lots of other questions I could ask, but those questions
are based on the fact that this _looks_ like a classic cache blowout. I.e.,
the query runs quickly when all the related data is in RAM, but is
significantly slower when the data has to be pulled from disk. Answering
the quesitons above will likely help to determine if my guess is correct.

If my guess is correct, there are any number of potential ways to improve
things: Add RAM to the machine, enlarge shared_buffers, put a geo index on
public.sthelens.points so it doesn't have to scan the entire table; as a
few examples.

Understanding what pc_typmod_pcid() actually does would help, but even
without that you can test things in a few ways. One would be to substitute
a different query in your testing for select pc_typmod_pcid(1) that is
known to push the contents of public.sthelens out of memory and see if
the behavior is similar. Any count(*) query on some other large table
would probably suffice. A better way would probalby be to install the
pg_buffercache module and see what's actually in the cache at each step
of the testing process.

In any event, if your testing doesn't help any; you'll probably need to
include answers to at least the above questions before the list will be
much help.

That is, of course, unless someone familar with pointcloud has seen this
exact problem and already knows the answer ...

--
Bill Moran <wmoran@potentialtech.com>