On 03/21/2018 05:09 PM, Alessandro Aste wrote: > Hi there, we are using postgresql 10.3 and we're facing an issue with a > query. The query (full query below) completes only when: > > 1 - LIMIT 10 is removed > or > 2 - show max_parallel_workers_per_gather is set to 0, so parallel > processing is disabled. > > With max_parallel_workers_per_gather set to the default value (8) I'm > not even able to get the query plan. > > Notes: > > * We're experiencing the issue in any server of ours but I've > reproduced the issue in a fresh restored database with full > vacuum/reindex of the tables. > * We didn't touch any parameter concering the parallel processing, > we're running the defaults: > > > cmdstaging=# show max_parallel_workers_per_gather ; > max_parallel_workers_per_gather > --------------------------------- > 8 > (1 row) > > cmdstaging=# show max_worker_processes ; > max_worker_processes > ---------------------- > 8 > (1 row) > > cmdstaging=# show max_parallel_workers; > max_parallel_workers > ---------------------- > 8 > (1 row) > > > > > The query completes only omitting the LIMIT clause or when I disable > parallel processing: > > 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 | | GTT/POP/LON1T | Active | LON1T | > 12288 | Telehouse UK | 14 Coriander Avenue | London | E14 > 2AA | | GB | 219 > (1 row) > > Time: 4374.759 ms (00:04.375) > cmdstaging=# show max_parallel_workers_per_gather ; > max_parallel_workers_per_gather > --------------------------------- > 0 > (1 row) > > Time: 0.097 ms > > > Otherwise it just keep running for forever. > When you say "running forever" is it actually using CPU, or does it get stuck on something? > > This is the full query: > > > 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 <http://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 <http://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 <http://so.id> = pop.service_order_id > left JOIN client_site cs on cs.id <http://cs.id>=so.a_site_id > left JOIN country csc on csc.id <http://csc.id>=cs.country_id > JOIN circuit c ON so.product_id=c.product_id > JOIN circuit_status cst ON cst.id <http://cst.id>=c.status > JOIN ( > SELECT c.id <http://c.id> AS circuit_id, sg.id <http://sg.id> AS segment_id, c.pop_support_vendor_id AS vendor_id, > v.name <http://v.name> AS vendor_name, sg.a_company_name > FROM segment sg > JOIN circuit_layout cl ON cl.segment_id = sg.id <http://sg.id> AND cl.ordinal = 1 > JOIN circuit c ON c.id <http://c.id> = cl.circuit_id > JOIN vendor v ON v.id <http://v.id> = c.pop_support_vendor_id > ) seg ON seg.circuit_id = c.id <http://c.id> > JOIN vendor_gtt_pop on vendor_gtt_pop.gtt_pop_id = seg.circuit_id > ) foo where vendor_id = 12346 AND (1=1) ORDER BY pop_name ASC,id LIMIT 10 > > > > Execution plan with max_parallel_workers_per_gather =0 , > max_parallel_workers_per_gather =8 and no LIMIT clause : > > We really need to see the execution plan that causes issues, i.e. max_parallel_workers_per_gather=8 with LIMIT clause. Plain explain (without analyze), at least. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
