Re: Pg_locks and pg_stat_activity

Hi Kyotaro,
Many thanks for the response. Will try and debug further. Have responded to
Justin in another chain. Could you please check and advise if anything ?

Regards,
Aditya.

On Fri, Dec 4, 2020 at 1:23 PM Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com>
wrote:

> At Fri, 4 Dec 2020 12:11:59 +0530, Ravikumar Reddy <
> urravikumarreddy(at)gmail(dot)com> wrote in
> > Hi,
> > pg_stat_activity -- Providers the active and ideal connection for our
> > database
> > Pg_locks -- Provider the queries/procedure/function details if
> > any object is locked at the current in our database.
>
> Yeah..
>
> That result is quite hard to see, but..
>
> > On Fri, Dec 4, 2020 at 11:43 AM aditya desai <admad123(at)gmail(dot)com> wrote:
> >
> > > Hi Postgres Experts,
> > > Requesting for advice on below. I am new to postgres :(
> > >
> > > Regards,
> > > Aditya.
> > >
> > > On Tue, Dec 1, 2020 at 9:41 PM aditya desai <admad123(at)gmail(dot)com>
> wrote:
> > >
> > >> Hi,
> > >> One of the API is calling three of the below queries. Output(jobids)
> of
> > >> 1st query gets fed to 2nd query in API.
> > >>
> > >> Query 1:
> > >>
> > >> select j.id from job j where $19=$20 and j.internaljobcode in
> > >> ($21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31) and j.countrycode =
> $1 and
> > >> j.facilitycode in ($2) and j.jobstatuscode in ($3, $4, $5, $6) and
> > >> j.internaljobcode in ($7, $8, $9, $10, $11, $12, $13, $14, $15) and
> > >> ((j.jobstartdatetime between $16 and $17) or j.jobstartdatetime IS
> NULL)
> > >> ORDER BY createddate DESC limit $18"
> > >>
> > >> Query 2
> > >>
> > >> with JobData AS ( select * from job where id in ($1, $2, $3, $4, $5,
> $6,
> > >> $7, $8, $9, $10) )
> > >> select j.id
> > >>
> ,j.jobcategory,j.internaljobcode,j.jobname,j.jobstatuscode,j.jobreferencenumber,
> > >>
> > >>
> vws.vascodes,j.createddate,j.facilitycode,j.countrycode,j.sladate,j.codamount,j.jobstartdatetime,j.jobenddatetime,j.attemptcount,
> > >> j.primeindicator,j.rescheduleddatetime,j.jobproductcode,
> > >> j.tour_id, j.pickupaccount,
> > >>
> j.connoterequired,j.expectedbags,j.expectedparcels,j.isservicepointpickup,
> > >>
> > >>
> j.estimateddeliverydatetime,j.currency,j.paymentmethod,j.paymentamount,j.missorted,j.pickupcustomername,j.mps,j.parcelcount,j.jobcontactpersonname,t.courier_id,t.tourid,
> > >> js.jobstatusname, jt.externaljobcode, ja.city, ja.postalcode,
> > >> ja.addressline1, ja.addressline2,
> > >> ja.addressline3,ja.addresstype, ja.state
> > >> from JobData j join jobaddress ja on ja.job_id=j.id join
> > >> jobstatus js on js.jobstatuscode=j.jobstatuscode
> > >> join jobtype jt on j.internaljobcode=jt.internaljobcode
> left
> > >> join
> > >> (select v.job_id, string_agg(distinct(v.code),'PPOD') as
> > >> vascodes from JobData j join valueaddedservices v on j.id=v.job_id
> > >> group by v.job_id) AS vws on vws.job_id=j.id left join tour t on t.id
> =j.tour_id
> > >> and ((j.internaljobcode in ('003','012') and ja.addresstype='RETURN')
> or
> > >> j.internaljobcode not in ('003','012')) ORDER BY id DESC ;
> > >>
> > >> Query3:
> > >>
> > >> "with JobCount as ( select jobstatuscode,count($14) stat_count from
> job j
> > >> where $15=$16 and j.countrycode = $1 and j.facilitycode in ($2) and
> > >> j.internaljobcode in ($3, $4, $5, $6, $7, $8, $9, $10, $11) and
> > >> ((j.jobstartdatetime between $12 and $13) or j.jobstartdatetime IS
> NULL)
> > >> group by j.jobstatuscode)
> > >> select js.jobstatuscode,COALESCE(stat_count,$17) stat_count from
> JobCount
> > >> jc right outer join jobstatus js on jc.jobstatuscode=js.jobstatuscode"
> > >>
> > >>
> > >> When I run explain analyze for 1st two queries Execution Time is
> below 1
> > >> milliseconds for these queries. Basically queries run fast and with
> low
> > >> cost when ran from Database 'psql' or pgadmin. However when called
> from API
> > >> Average Time in pg_stat_statements shows more than 1 second. When
> Load test
> > >> runs these queries get concurrently called ,response time beomes poor
> with
> > >> more load. Could this be due to Lockings.
> > >>
> > >>
> > >> I checked pg_locks and I see the below records. Query that I used is
> also
> > >> given below. I could see few ExclusiveLocks for "virtualxid" records
> and
> > >> for queries with CTEs(WITH Clause). Please advise
> > >>
> > >>
> > >>
> > >> SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa
> > >> ON pl.pid = psa.pid;
>
> <snip>
>
> You would find that the "granted" column in all the rows from pg_locks
> is "true", that is, no one is waiting on a lock. That slowdown doesn't
> at least seem coming from lock conflict.
>
> regards.
>
> --
> Kyotaro Horiguchi
> NTT Open Source Software Center
>