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 wrote: > At Fri, 4 Dec 2020 12:11:59 +0530, Ravikumar Reddy < > [email protected]> 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 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 > 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; > > > > 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 >
Re: Pg_locks and pg_stat_activity
On Fri, Dec 04, 2020 at 01:31:14PM +0530, aditya desai wrote: > Hi Justin, > Many thanks for your response. Please see my response below. > > What do you mean by API ? If it's a different client, how does it connect ? > Queries are getting called from Web UI built in Microservices spring boot. > It connected to Database with JDBC driver. Developers have handled > connection pooling at the Application side. > > What db driver ? > > Driver is JDBC I suspect JDBC is using parameterized queries (postgres $1, $2, ..) and psql isn't (unless you type "prepare p AS SELECT ..." and execute p(.., .., ..)" You can search and find other people who reported similar issues. https://www.postgresql.org/message-id/flat/A737B7A37273E048B164557ADEF4A58B50FB8D5E%40ntex2010i.host.magwien.gv.at https://www.postgresql.org/message-id/flat/20200504191201.GU28974%40telsasoft.com I don't know what server version you have, so I don't know whether to suggest testing with plan_cache_mode=force_custom_plan -- Justin
Re: Pg_locks and pg_stat_activity
Hi Justin, Thanks for your response. I suspect JDBC is using parameterized queries (postgres $1, $2, ..) and psql isn't (unless you type "prepare p AS SELECT ..." and execute p(.., .., ..)" Yes JDBC is using parameterized queries which get constructed dynamically depending upon user privileges in the application.Does this cause any issues? I don't know what server version you have, so I don't know whether to suggest testing with plan_cache_mode=force_custom_plan It's Postgres 11.5. I will look into links you shared. Regards, AD. On Fri, Dec 4, 2020 at 1:47 PM Justin Pryzby wrote: > On Fri, Dec 04, 2020 at 01:31:14PM +0530, aditya desai wrote: > > Hi Justin, > > Many thanks for your response. Please see my response below. > > > > What do you mean by API ? If it's a different client, how does it > connect ? > > Queries are getting called from Web UI built in Microservices spring > boot. > > It connected to Database with JDBC driver. Developers have handled > > connection pooling at the Application side. > > > > What db driver ? > > > > Driver is JDBC > > I suspect JDBC is using parameterized queries (postgres $1, $2, ..) and > psql > isn't (unless you type "prepare p AS SELECT ..." and execute p(.., .., ..)" > > You can search and find other people who reported similar issues. > > https://www.postgresql.org/message-id/flat/A737B7A37273E048B164557ADEF4A58B50FB8D5E%40ntex2010i.host.magwien.gv.at > > https://www.postgresql.org/message-id/flat/20200504191201.GU28974%40telsasoft.com > > I don't know what server version you have, so I don't know whether to > suggest > testing with plan_cache_mode=force_custom_plan > > -- > Justin >
Index for range queries on JSON (user defined fields)
Hello! We have a multi-tenant service where each customer has millions of users (total: ~150M rows). Now we would like to let each customer define some custom columns for his users and then let the customer search his users efficiently based on these columns. This problem seems really hard to solve with PostgreSQL: https://stackoverflow.com/questions/5106335/how-to-design-a-database-for-user-defined-fields In particular the easiest way would be to add a JSON field on the users table (e.g. user metadata). However the PostgreSQL GIN index only supports exact matches and not range queries. This means that a query on a range (e.g. age > 30) would be extremely inefficient and would result in a table scan. Algorithmically it seems possible to use a GIN index (based on btree) for a range query. Also MongoDB seems to support something similar ( https://docs.mongodb.com/manual/core/index-wildcard/). Are there any plans to add support for range queries to GIN indexes (on JSON) in the future versions of PostgreSQL? Marco Colli Pushpad
PostgeSQL JSONB Column with various type of data
Hi Team,
We are using Postgresql JSONB as storage type in our development.
In the below table , RECORD column has JSONB data and we create a view which
will derive the column "TEST_MV_2" from column "RECORD" as below
CREATE OR REPLACE VIEW public."V_TEST_SELECT"
AS
SELECT a.recid,a.record AS "RECORD",
jsonb_path_query(a.xmlrecord, '$."2"'::jsonpath) AS "TEST_MV_2 "
FROM " TEST_SELECT " a;
So we might have array of data or an empty JSON object or an array of empty
JSON object or a string in the column "TEST_MV_2".
Null is stored as empty JSON object due to our business logic.
RECID
RECORD (datatype: JSONB)
TEST_MV_2 (datatype: JSONB)
"SELTEST1"
"{"1": "SELTEST1", "2": [{"": "TESTVALUE"}, {}]}"
[{"": "TESTVALUE"}, {}]
"SELTEST2"
"{"1": "SELTEST2", "2": "TESTVALUE"}"
"TESTVALUE"
"SELTEST3"
"{"1": "SELTEST3", "2": [{"": "TESTVALUE"}, {"": "TESTVALUE1"}]}"
[{"": "TESTVALUE"}, {"": "TESTVALUE1"}]
"SELTEST4"
"{"1": "SELTEST4", "2": [{"": "TESTVALUE4MV1"}, {}]}"
[{"": "TESTVALUE4MV1"}, {}]
"SELTEST5"
"{"1": "SELTEST5", "2": [{}, {}]}"
[{},{}]
"SELTEST6"
"{"1": "SELTEST6", "2": {}}"
{}
"SELTEST7"
"{"1": "SELTEST7", "2": [{}, {"": "TESTVALUE"}]}"
[{}, {"": "TESTVALUE"}]
In such cases, to find the null values in the JSONB, I have written below SQL
Function to handle different type of data
CREATE OR REPLACE FUNCTION jsonbNull(jsonb_column JSONB)
returns boolean as $$
declare
isPoint text := jsonb_typeof(jsonb_column) ;
begin
CASE isPoint
WHEN 'array' THEN
if true = ALL(select
(jsonb_array_elements(jsonb_column)) = '{}') THEN
return true;
else
return false;
end if;
WHEN 'object' THEN
if jsonb_column = '{}' THEN
return true;
else
return false;
end if;
WHEN 'string' THEN
return false;
ELSE
return true;
END CASE;
end;
$$ LANGUAGE plpgsql IMMUTABLE;
Sample SQL statement used:
SELECT RECID,"TEST_MV_2" FROM "V_TEST_SELECT" WHERE true=jsonbNull("TEST_MV_2")
ORDER BY RECID ;
I would like to know whether we can handle multiple types of JSONB data in a
better/nicer way as this function could impact performance of the query.
Kindly provide your suggestions.
Thanks,
[cid:[email protected]]
RISWANA
Technical Lead
TEMENOS India
Sterling Road, Chennai
d: + 91 9943613190
[cid:[email protected]][cid:[email protected]][cid:[email protected]]
temenos.com
The information in this e-mail and any attachments is confidential and may be
legally privileged. It is intended solely for the addressee or addressees. Any
use or disclosure of the contents of this e-mail/attachments by a not intended
recipient is unauthorized and may be unlawful. If you have received this e-mail
in error please notify the sender. Please note that any views or opinions
presented in this e-mail are solely those of the author and do not necessarily
represent those of TEMENOS. We recommend that you check this e-mail and any
attachments against viruses. TEMENOS accepts no liability for any damage caused
by any malicious code or virus transmitted by this e-mail.
Re: PostgeSQL JSONB Column with various type of data
On Fri, Dec 4, 2020 at 9:21 AM Riswana Rahman wrote:
> CREATE OR REPLACE FUNCTION jsonbNull(jsonb_column JSONB)
>
> returns boolean as $$
>
> declare
>
> isPoint text := jsonb_typeof(jsonb_column) ;
>
> begin
>
> CASE isPoint
>
> WHEN 'array' THEN
>
>if true = ALL(select
> (jsonb_array_elements(jsonb_column)) = '{}') THEN
>
> return true;
>
>else
>
>return false;
>
>end if;
>
> WHEN 'object' THEN
>
>if jsonb_column = '{}' THEN
>
> return true;
>
>else
>
> return false;
>
>end if;
>
> WHEN 'string' THEN
>
> return false;
>
> ELSE
>
>return true;
>
> END CASE;
>
> end;
>
> $$ LANGUAGE plpgsql IMMUTABLE;
>
As far as I can tell, it seems like this could be re-written as a function
in SQL instead of plpgsql which allows for it to be in-lined. Have you
tested performance and found it to be an issue, or just optimizing in
advance of a need?
>
Re: Index for range queries on JSON (user defined fields)
On Fri, 4 Dec 2020 at 15:39, Marco Colli wrote: > Hello! > > We have a multi-tenant service where each customer has millions of users > (total: ~150M rows). Now we would like to let each customer define some > custom columns for his users and then let the customer search his users > efficiently based on these columns. > > This problem seems really hard to solve with PostgreSQL: > > https://stackoverflow.com/questions/5106335/how-to-design-a-database-for-user-defined-fields > > In particular the easiest way would be to add a JSON field on the users > table (e.g. user metadata). However the PostgreSQL GIN index only supports > exact matches and not range queries. This means that a query on a range > (e.g. age > 30) would be extremely inefficient and would result in a table > scan. > You could have a table of (tenant, customer, setting_name, setting_value) so that a btree index on (tenant, setting_name, setting_value) would work for "select customer from my_table where tenant=$1 and setting_name='age' and setting_value > 30" That doesn't deal with setting values having a variety of types, but you could have a distinct user defined settings table for each setting value type that you want to support.
