Re: Postgresql 10.3 , query never completes if LIMIT clause is specified and paralle processing is on

On 03/21/2018 08:44 PM, Alessandro Aste wrote:
> Thanks for your reply Tomas.  The query just got stuck for forever.  I
> observed no CPU spikes, it is currently running and I see 89 of the CPU
> idle. I'm using a relative powerfull machine (120 GB ram, 56 CPU).  
>

That doesn't really answer the question, I'm afraid. I suppose "89 of
CPU" means that 89% idle in total, but 11% with 56 CPUs still means
about 6 cores 100% busy. But maybe you meant something else?

Is there something else running on the machine? If you look at "top" are
the processes (the one you're connected to and the parallel workers)
doing something on the CPU?

>
> Plain analyze as requested. : 
>

I don't see anything obviously broken with the query plan, and it's
difficult to compare with the other plans because they are quite different.

But there's one part of the plan interesting:

Limit (cost=253523.56..253523.57 rows=1 width=176)
-> Sort (cost=253523.56..253523.57 rows=1 width=176)
Sort Key: c_2.gii_circuitid, c_1.id
-> Nested Loop (cost=33190.89..253523.55 rows=1 width=176)
Join Filter: (c_1.id = c.id)
-> Nested Loop (cost=31724.87..31736.29 rows=1 ...)
...
-> Gather (cost=1466.02..221787.23 rows=3 width=75)
Workers Planned: 5
-> Hash Join (cost=466.02..220786.93 rows=1 ...)
...

That is, there's a Gather on the inner side of a Nested Loop. I wonder
if that might cause issues in case of under-estimate (in which case we'd
be restarting the Gather many times) ...

BTW one of the plans you sent earlier is incomplete, because it ends
like this:

-> Nested Loop (cost=42469.41..42480.82 rows=1 width=85) (...)
Join Filter: (c.status = cst.id)
Time: 3016.688 ms (00:03.017)

That is, it's missing the part below the join.

That being said, I'm not sure what's the issue here. Can you prepare a
self-contained test case that we might use to reproduce the issue? For
example by dumping the relevant part of the tables?

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services