Re: Postgresql 10.3 , query never completes if LIMIT clause isspecified and paralle processing is on - Mailing list pgsql-general

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)