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

[Alessandro Aste <alessandro.aste@xxxxxxxxx>]

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.aste@xxxxxxxxx> 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.vondra@xxxxxxxxxxxxxxx> 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