Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g0sgI-0000T1-Tw for pgsql-performance@arkaria.postgresql.org; Fri, 14 Sep 2018 18:19:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1g0sgH-0007Jj-99 for pgsql-performance@arkaria.postgresql.org; Fri, 14 Sep 2018 18:19:01 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g0sgG-0007JR-SL for pgsql-performance@lists.postgresql.org; Fri, 14 Sep 2018 18:19:01 +0000 Received: from mail-ed1-x530.google.com ([2a00:1450:4864:20::530]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1g0sgE-0000tp-Bl for pgsql-performance@lists.postgresql.org; Fri, 14 Sep 2018 18:18:59 +0000 Received: by mail-ed1-x530.google.com with SMTP id p52-v6so8109728eda.12 for ; Fri, 14 Sep 2018 11:18:58 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:mime-version:from:in-reply-to:date:cc:message-id:references :to; bh=ia+sL3E5229Yxg6KnB+2a5YwRab56QSehDFyWeTiAy8=; b=goji15tuuasYNDaOomzNNcI0QAToZDGFICMkmPt3lhaGe++vloOHjoxHAbMWjXaxVE Z0kDDKbXE9HRKTxy8clMv4uQpx7SaS3KIC1Qy9rAgnfVdFejpYWYuUZ+IKUOKK1+GCFU tRL5iCkalswkjjgR9LjwCXmvkvSOSblOKZBEp/Aq/tRR1Zg5u1emREnU1+nVI2i/dfJk mnkj7aaK5LPG2+33tifndrtrP9JopugxFP+WbBv7oj2GD8cD5c+2xuB6MyNZI4ZVX9Ui Ktkspz6oSncAst2Hm1oAgBvsvqV2NuncMbkYWdyy1DsXns8Mziy62Nv+kl2EpVrJTMXO PA1w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:mime-version:from:in-reply-to:date:cc :message-id:references:to; bh=ia+sL3E5229Yxg6KnB+2a5YwRab56QSehDFyWeTiAy8=; b=TKHHOnT5rqHiBRUF1A41wpDp5buR4SIQsK1pastui3tvB2mURDVNerBmdm972OD7vd g6XHfESVTUYUQh8y54XLcQq8CnZFW2pqQpwM4xvmgaUDem5tsqzailPgJonDGXf4oCQV pVnrwKE8MDShxnVIUzoqW4O94nl1/mVpGTGrqJZSt8MT/myOfGB6cH7FtUeUzzi18HnX ql7DqPvrnmtBiJa3ecyyUd4FkHhXsn2BTLXp1ZriZRyvNxWTh/MyD8ncP6da2P8LsSSc kSy/UUrht/RfgVRdbcm9RgNXd8r/yxvT4uy5QJmYGH3/GsDtV2OZgog4h/J84jClk1kj xEOw== X-Gm-Message-State: APzg51Cz1dK+C2G7GcsU5wfPRdvlIHJYqpoJdBPDG98SQz/dS2KDjPO+ G1mncx7+c6M1pIPpstHogGg= X-Google-Smtp-Source: ANB0VdawuNdaFSTZxgkcBJ7ReGkpf2FLzIWnUKXjDRxrIXIonRFjFm6pmGMA2f/A/wq+691agazjJg== X-Received: by 2002:a50:871c:: with SMTP id i28-v6mr23157319edb.13.1536949136694; Fri, 14 Sep 2018 11:18:56 -0700 (PDT) Received: from ip-172-20-1-26.eu-west-1.compute.internal (ec2-52-209-59-238.eu-west-1.compute.amazonaws.com. [52.209.59.238]) by smtp.gmail.com with ESMTPSA id h34-v6sm3537079eda.58.2018.09.14.11.18.55 (version=TLS1 cipher=ECDHE-RSA-AES128-SHA bits=128/128); Fri, 14 Sep 2018 11:18:55 -0700 (PDT) Subject: Re: How Do You Associate a Query With its Invoking Procedure? Mime-Version: 1.0 (Mac OS X Mail 9.3 \(3124\)) Content-Type: multipart/alternative; boundary="Apple-Mail=_63EB2278-60E9-4E76-ADBD-BABC42D7C627" From: Roman Konoval X-Priority: 3 In-Reply-To: <5b9abf54.1c69fb81.8e0e4.9e2d@mx.google.com> Date: Fri, 14 Sep 2018 20:18:55 +0200 Cc: "pgsql-performance@lists.postgresql.org" Message-Id: References: <5b9abf54.1c69fb81.8e0e4.9e2d@mx.google.com> To: Fd Habash X-Mailer: Apple Mail (2.3124) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_63EB2278-60E9-4E76-ADBD-BABC42D7C627 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 If you can change the application then one option is to set = application_name so that it contains API function name. This should = happen before the first call in API function hits the database. After = the API function finishes it should reset application_name. Then you can enable logging of all queries and set the format to include = application_name parameter. This way every query is logged and each log = entry has an application name. Several things to keep in mind: 1. logging everything may affect performance 2. application_name is 64 chars by default Regards, Roman Konoval rkonoval@gmail.com > On Sep 13, 2018, at 21:49, Fd Habash wrote: >=20 > In API function may invoke 10 queries. Ideally, I would like to know = what queries are invoked by it and how long each took.=20 > =20 > I=E2=80=99m using pg_stat_statement. I can see the API function = statement, but how do I deterministically identify all queries invoked = by it? > =20 > =20 > ---------------- > Thank you --Apple-Mail=_63EB2278-60E9-4E76-ADBD-BABC42D7C627 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
If you can change the application then one = option is to set application_name so that it contains API function name. = This should happen before the first call in API function hits the = database. After the API function finishes it should reset = application_name.

Then you can enable logging of all queries and set the format = to include application_name parameter. This way every query is logged = and each log entry has an application name.

Several things to keep in = mind:
1. logging everything may affect = performance
2. application_name is 64 chars by = default

Regards,
Roman Konoval
rkonoval@gmail.com


On Sep 13, 2018, at 21:49, Fd Habash <fmhabash@gmail.com> = wrote:

In API function may invoke = 10 queries. Ideally, I would like to know what queries are invoked by it = and how long each took. 
 
I=E2=80=99m using pg_stat_statement. I can see the API = function statement, but how do I deterministically identify all queries = invoked by it?
 
 
----------------
Thank = you

= --Apple-Mail=_63EB2278-60E9-4E76-ADBD-BABC42D7C627--