Re: Postgres sometimes stalling on 'percentile_cont'

Tom van Tilburg <tom(dot)van(dot)tilburg(at)gmail(dot)com> writes:
> 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.

Hard to say for sure with just this much detail, but what this smells
like is a bad query plan choice based on out-of-date statistics. The
fact that the query is fast when you retry could then be explained by
supposing that the autovacuum daemon has gotten in there and updated
the stats while you were waiting. So I'd try inserting a manual ANALYZE
of the table(s) that the earlier queries modify.

If that doesn't fix it, we'd need much more detail to offer help. See

https://wiki.postgresql.org/wiki/Slow_Query_Questions

It'd be particularly useful to compare EXPLAIN output in both the
"slow" and "fast" states.

regards, tom lane