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

Hi,

the perf profiles look really weird - clearly, there's a lot of lock
contention, because the top item is this

13.49% 13.40% postmaster postgres [.] LWLockAcquire
|

---LWLockAcquire

That's a sign of lock contention - not sure which one, though. And then
there's heap_hot_search_buffer

12.65% 7.92% postmaster postgres [.] heap_hot_search_buffer
|

---heap_hot_search_buffer

So either there's a table with many HOT-updated rows (repeatedly), or we
search the HOT chains very frequently for some reason. Considering it
only affects the non-LIMIT case, I'd guess the latter.

There also seems to be quite a few page faults, for some reason.

Trying to reproduce this without the actual data is rather futile. We
need some sort of reproducer (sample data to test the query on).

regards

On 03/28/2018 10:30 AM, Alessandro Aste wrote:
> Hello,  any news ?
>
> Thank you,
>
> Alessandro.
>
> On Fri, Mar 23, 2018 at 8:22 PM, Alessandro Aste
> <alessandro(dot)aste(at)gmail(dot)com <mailto: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
> <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
> <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 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 <mailto: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
> <mailto: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
>
>
>
>

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services