Search Postgresql Archives

Postgres sometimes stalling on 'percentile_cont'

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi,

I have a set of relatively complex queries producing tables (including postgis and pgpointcloud functions) that I run consecutively, and sometimes (depending on the base-data) my 5th query stalls (CPU 100%, runs forever) seemingly on the percentile_cont function. When I replace percentile_cont with just a value it passes.

The setup roughly looks like this:
query1 - creates 77 records with polygons
query2 - creates 89 records with polygons
query3 - creates ~350k records with points (inside above polygons)
query4 - creates ~220k records with points clustered by height (from result query3)
query5 - creates ~102k records with point clustered by normal (from result query3)

The odd thing is, when I run query5 directly after query4, it will stall on some datasets (always same sets). Though when I cancel the query and run it again, it will pass in about 2 seconds.
When I run  query5 from a different shell directly after query4, it also passes.

Does anyone have a clue on how to research this further? How can I look into the process itself to see what is cycling the CPU? How to break this problem down to smaller chunks? I am a bit out of options myself.

For what it's worth:

SELECT version();
"PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 6.3.0-12ubuntu2) 6.3.0 20170406, 64-bit"

SELECT postgis_full_version();
"POSTGIS="2.5.0beta1dev r16609" [EXTENSION] PGSQL="100" GEOS="3.6.2-CAPI-1.10.2 4d2925d6" SFCGAL="1.3.1" PROJ="Rel. 5.1.0, June 1st, 2018" GDAL="GDAL 2.3.1, released 2018/06/22" LIBXML="2.9.4" LIBJSON="0.12.1" LIBPROTOBUF="1.2.1" RASTER"

The part of query5 where I replace percentile_cont with just a value:
SELECT
    c1.id,c1.cid, c1.blockid,
    CASE
        WHEN aspectmedian > 360 THEN aspectmedian - 360
        ELSE aspectmedian
    END as aspect,
    pt geom
FROM clustered c1
JOIN (
    SELECT cid, blockid, aspectclass,
        --find median aspect and median height within bucket
        --percentile_cont(0.5) within group (order by aspect) as aspectmedian
        0 as aspectmedian --replaced by value 0
    FROM clustered
    GROUP BY blockid,cid, aspectclass
) c2 ON (c1.cid = c2.cid AND c1.blockid = c2.blockid AND c1.aspectclass = c2.aspectclass)
WHERE c1.cid Is Not Null;



Best,
 Tom

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux