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

Tomas, thank you. This machine is abare metal server running only a
staging postgresql 10.3 instance. Nobody is using it beside me.

I'm attaching 4 files.

every_30_seconds_top_stats_during_query.txt - this is a caputure of the
top command every 30 seconds(more or less) for 10+ minutes while I'm
running the query. Let me know if this helps to answere your question.
EXPLAIN_WITH_LIMIT_AND_max_parallel_workers_per_gather_SET_TO_0.txt - query
plan with full query and max_parallel_workers_per_gather force to 0.
Full output.
EXPLAIN_WITH_LIMIT_AND_DEFAULT_PARALLEL_PROCESSING_SETTINGS.txt - query
plan with full query and default parellel processing settings. Full
output.
EXPLAIN_WITHOUT_LIMIT_AND_DEFAULT_PARALLEL_PROCESSING_SETTINGS.tx - query
plan of the query omitting the LIMIT clause and default parellel processing
settings. Full output.

For what concerns the self-contained test case - I'll do my best to
prepare it.

Thank you very much, please let me know if this answer your questions.

Il 22 mar 2018 3:04 AM, "Tomas Vondra" <tomas(dot)vondra(at)2ndquadrant(dot)com> ha
scritto:

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