From: | Eric Lemoine <eric(dot)lemoine(at)oslandia(dot)com> |
---|---|
To: | Bill Moran <wmoran(at)potentialtech(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Performance issue with Pointcloud extension |
Date: | 2017-06-08 16:40:17 |
Message-ID: | 386038d7-24ce-8b01-73ff-31947afef011@oslandia.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> Lots of missing information here ...
>
> Is there an index on public.sthelens.points?
Yes, there are.
lopocs=# \d sthelens;
Table "public.sthelens"
Column | Type | Modifiers
--------+------------+-------------------------------------------------------
id | integer | not null default nextval('sthelens_id_seq'::regclass)
points | pcpatch(2) |
morton | bigint |
Indexes:
"sthelens_pkey" PRIMARY KEY, btree (id)
"sthelens_pc_envelopegeometry_idx" gist (pc_envelopegeometry(points))
So two indices, one for the primary key, and a Postgis index on the
Postgis geometry returned by the Pointcloud pc_envelopegeometry function.
> How many rows are in that table?
30971
> What are your shared_buffers settings?
128 MB (Debian unstable)
> How much RAM does the server have?
16 GB
> What does EXPLAIN look like for that query? How large (in bytes) are the
> tables in question?
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on sthelens (cost=383.26..2496.67 rows=2065 width=32)
(actual time=3.213..46.674 rows=2506 loops=1)
Recheck Cond:
('01030000207213000001000000050000002E47BC352BFC41C164910500DF8D4CC12E47BC75CCF941C164910500DF8D4CC12E47BC75CCF941C1649105002F8B4CC12E47BC352BFC41C1649105002F8B4CC12E47BC352BFC41C164910500DF8D4CC1'::geometry
&& st_geomfromewkb(pc_envelopeasbinary(points)))
Filter:
_st_intersects('01030000207213000001000000050000002E47BC352BFC41C164910500DF8D4CC12E47BC75CCF941C164910500DF8D4CC12E47BC75CCF941C1649105002F8B4CC12E47BC352BFC41C1649105002F8B4CC12E47BC352BFC41C164910500DF8D4CC1'::geometry,
st_geomfromewkb(pc_envelopeasbinary(points)))
Heap Blocks: exact=36
-> Bitmap Index Scan on sthelens_pc_envelopegeometry_idx
(cost=0.00..382.75 rows=6196 width=0) (actual time=1.626..1.626
rows=2506 loops=1)
Index Cond:
('01030000207213000001000000050000002E47BC352BFC41C164910500DF8D4CC12E47BC75CCF941C164910500DF8D4CC12E47BC75CCF941C1649105002F8B4CC12E47BC352BFC41C1649105002F8B4CC12E47BC352BFC41C164910500DF8D4CC1'::geometry
&& st_geomfromewkb(pc_envelopeasbinary(points)))
Planning time: 0.525 ms
Execution time: 46.999 ms
(8 rows)
Note that the execution time is 46 ms when the query is wrapped in an
explain analyze (while it's 3 s when it's not!)
> What does pc_typmod_pcid() actually do?
It is one of the simplest functions of Pointcloud.
See
<https://github.com/pgpointcloud/pointcloud/blob/master/pgsql/pointcloud.sql.in#L44-L47>
and
<https://github.com/pgpointcloud/pointcloud/blob/eb4fe1e923179ad1ca718d5620b2f41ee1a94886/pgsql/pc_pgsql.c#L132-L139>.
>
> 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 d> ata 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.
I don't know. The query is fast if I run it first on the database
connection. And it is *always* very slow after the "select
pc_typmod_pcid(1)" query has run.
>
> 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.
It sounds like a performance issue to you, while it sounds like a bug to
me :)
>
> 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.
I'll try to use pg_buffercache.
Thanks a lot for your response. That at least gives me courage in
debugging this :)
--
Éric Lemoine
Oslandia
+33 1 86 95 95 55
Attachment | Content-Type | Size |
---|---|---|
eric_lemoine.vcf | text/x-vcard | 209 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Eric Lemoine | 2017-06-08 17:10:50 | Re: Performance issue with Pointcloud extension |
Previous Message | Adrian Klaver | 2017-06-08 16:23:41 | Re: Performance issue with Pointcloud extension |