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

Hello, any news ?

Thank you,

Alessandro.

On Fri, Mar 23, 2018 at 8:22 PM, Alessandro Aste <alessandro(dot)aste(at)gmail(dot)com>
wrote:

> PS , in the meanwhile I discovered a 2nd workaround(beside disabling
> parallel processing) . I added offset 0 to the subquery , and, according
> to the documentation, “OFFSET 0 is the same as omitting the OFFSET clause”
> - https://www.postgresql.org/docs/current/static/queries-limit.html
>
> cmd3dev=# show max_parallel_workers_per_gather ;
>
> *max_parallel_workers_per_gather*
>
> *---------------------------------*
>
> *8*
>
> (1 row)
>
>
>
> cmd3dev=# \timing
>
> Timing is on.
>
> cmd3dev=# SELECT * FROM (SELECT seg.circuit_id AS id,
> vendor_gtt_pop.vendor_id, CASE WHEN vendor_gtt_pop.push_to_gaa = 1 THEN 'Y'
> END as gaa, pop.gii_circuitid AS pop_name, cst.label AS pop_status,
> seg.a_company_name AS pop_location, seg.vendor_id AS pop_provider_id,
> seg.vendor_name AS pop_provider, cs.address1 AS pop_street, cs.city AS
> pop_city, cs.postal_code AS pop_postal_code, cs.state AS pop_state,
> csc.code AS pop_country, cs.country_id AS pop_country_id FROM ( SELECT
> c.gii_circuitid, max(so.id) AS service_order_id FROM service_order so
> join circuit c on c.product_id=so.product_id join master_service_order mso
> on mso.id=so.master_service_order_id WHERE NOT (so.ordertype_id = 2 AND
> so.status <> 999) AND NOT (so.ordertype_id = 3 AND so.status <> 999) AND
> c.status >= 20 AND c.status not in (160,999) AND mso.client_id=11615 AND
> c.service_description=28 AND c.status!=160 GROUP BY c.gii_circuitid ) pop
> JOIN service_order so ON so.id = pop.service_order_id left JOIN
> client_site cs on cs.id=so.a_site_id left JOIN country csc on csc.id=cs.country_id
> JOIN circuit c ON so.product_id=c.product_id JOIN circuit_status cst ON
> cst.id=c.status JOIN ( SELECT c.id AS circuit_id, sg.id AS segment_id,
> c.pop_support_vendor_id AS vendor_id, v.name AS vendor_name,
> sg.a_company_name FROM segment sg JOIN circuit_layout cl ON cl.segment_id =
> sg.id AND cl.ordinal = 1 JOIN circuit c ON c.id = cl.circuit_id JOIN
> vendor v ON v.id = c.pop_support_vendor_id ) seg ON seg.circuit_id = c.id
> JOIN vendor_gtt_pop on vendor_gtt_pop.gtt_pop_id = seg.circuit_id offset 0)
> foo where vendor_id = 12346 AND (1=1) ORDER BY pop_name ASC,id desc LIMIT
> 10;
>
> id | vendor_id | gaa | pop_name | pop_status | pop_location |
> pop_provider_id | pop_provider | pop_street | pop_city | pop
>
> _postal_code | pop_state | pop_country | pop_country_id
>
> --------+-----------+-----+---------------+------------+----
> ----------+-----------------+--------------+----------------
> -----+----------+----
>
> -------------+-----------+-------------+----------------
>
> 684807 | 12346 | Y | GTT/POP/LON1T | Active | LON1T
> | 12288 | Telehouse UK | 14 Coriander Avenue | London | E14
>
> 2AA | | GB | 219
>
> (1 row)
>
>
>
> *Time: 2245.073 ms (00:02.245)*
>
>
>
> On Fri, Mar 23, 2018 at 9:31 AM, Alessandro Aste <
> alessandro(dot)aste(at)gmail(dot)com> wrote:
>
>> Tomas, I'm attaching a 4MB file with the perf report. Let me know if it
>> gets blocked, I'll shrink it to the first 1000 lines.
>>
>> Thank you,
>>
>> Alessandro.
>>
>> On Thu, Mar 22, 2018 at 11:42 PM, Tomas Vondra <
>> tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
>>
>>> On 03/22/2018 11:29 PM, Alessandro Aste wrote:
>>> > Thanks Tomas. We're currently building postgres from source. In order
>>> to
>>> > enable symbols, you want me to re-configure postres with
>>> --enable-debug
>>> > then run perf?
>>> >
>>>
>>> Yes.
>>>
>>> regards
>>>
>>> --
>>> Tomas Vondra http://www.2ndQuadrant.com
>>> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>>>
>>
>>
>