Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pPhWh-0004p1-F3 for pgsql-general@arkaria.postgresql.org; Wed, 08 Feb 2023 10:18:07 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pPhWg-00038m-6L for pgsql-general@arkaria.postgresql.org; Wed, 08 Feb 2023 10:18:06 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pPhWf-00038c-OZ for pgsql-general@lists.postgresql.org; Wed, 08 Feb 2023 10:18:05 +0000 Received: from mail-ej1-x636.google.com ([2a00:1450:4864:20::636]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pPhWd-0004bN-FH for pgsql-general@lists.postgresql.org; Wed, 08 Feb 2023 10:18:04 +0000 Received: by mail-ej1-x636.google.com with SMTP id ml19so50151344ejb.0 for ; Wed, 08 Feb 2023 02:18:03 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20210112.gappssmtp.com; s=20210112; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:from:to:cc:subject:date :message-id:reply-to; bh=GLyStwPYVuSMj2PVDAreyC479lhjj/uMBJsJtnRHZCQ=; b=dPqLAfSJxCuibUPQ4gvCmopNM6bV3D0eEcU2JlaoZ9qPKp98I993omF7zW6QeD8XiP R9VY2z5dT5RMAQrRiQLPIMAwL3Rj2oCZIm26/o2OGg5UtU9a6q254RgZXy9ehrpblacd /hqCta83pOmJcHkR0TKjZyT71L/k2nJwtCO8bTaDnJCB6y97ITVb2rNLRLNga7Sc4VLQ kGNdk6QzcG6w5CnFRTkdjsBqO8TLJ2vmd7R30BsU7hUpUOyJW1HisqXIFqWPrCrgic05 n8IMc+BB3bFXt05N+p+7rtDmQq4fpt1iM2Oi90voBWX3dLNr/n/XZtAXKYGEM7Gc1cqd fx4Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:x-gm-message-state:from :to:cc:subject:date:message-id:reply-to; bh=GLyStwPYVuSMj2PVDAreyC479lhjj/uMBJsJtnRHZCQ=; b=k5h42AsfNCj75X9orCVZxCVcr9O8EK520p0io5/D5MULf5Dvazmh04nbIcOqDsvxEb 7J0UoL65+GqKAFlC5KQNuZuxbnK+pcQ01Mur4K5rmZNJEJ+Uhv1y3uUTqAWxSknaP2wg vxZLwoV0PhqFkRjSGlePppsvoq0FoWo6n6YZpYcq9u+/pCNWRJzVr1CiWHXHOW25iIMm vAZu/vUfjFgUP+FQKRipyd+1P5nUWkWPIQ9YRW9Ww81tsW8vc3Gs/bkKOFQreFQE0Vzm CtxqhJKxCYOA7pXjFZ1DM5JJqK2S4rSWrhnX7nat4XqkoYQz6GNFNeGKHX7JdkhbCfps ionQ== X-Gm-Message-State: AO0yUKVUn9koqUv9amqLW0qcMMKqH1CUEAYKGXdOONoBRdQ/ZbLnefcg L0njR0hdjCtCZ8qBEyi5MCr3ObRJ7sNrhW2K X-Google-Smtp-Source: AK7set9OdeS1fwZtwTFc0BeDwqTID0QsL+bDLIXIS8GTddItHt69hmFA7K1yZNEfnX3ZusrIyM3xaA== X-Received: by 2002:a17:906:ce3b:b0:88a:cbd1:e657 with SMTP id sd27-20020a170906ce3b00b0088acbd1e657mr7011845ejb.5.1675851481997; Wed, 08 Feb 2023 02:18:01 -0800 (PST) Received: from localhost.localdomain ([46.183.103.8]) by smtp.gmail.com with ESMTPSA id md26-20020a170906ae9a00b008aeba83f15csm338461ejb.186.2023.02.08.02.18.01 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Wed, 08 Feb 2023 02:18:01 -0800 (PST) Message-ID: Subject: Re: How do a user-defined function that returns a table executes a query? From: Laurenz Albe To: Katsuya Okizaki , pgsql-general@lists.postgresql.org Date: Wed, 08 Feb 2023 11:18:00 +0100 In-Reply-To: References: Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.46.3 (3.46.3-1.fc37) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Wed, 2023-02-08 at 08:49 +0900, Katsuya Okizaki wrote: > In a normal SQL, we can use the EXPLAIN command to view the execution pla= n. > However, in this case, I am not sure how a user-defined function work. >=20 > If anyone has faced a similar situation and found a way to view the execu= tion plan, > I would greatly appreciate your insights and suggestions. I am not sure which of the following you want: 1. Get the execution plan of SQL statements run inside a function: For that, you would use "auto_explain" with "auto_explain.log_nested_sta= tements =3D on". 2. Get a trace of the execution of the function code itself: For PL/pgSQL, there is "pldebugger" (https://github.com/EnterpriseDB/pld= ebugger), which can be used together with pgAdmin. I usually sprinkle the code with RAISE NOTICE statements. Yours, Laurenz Albe --=20 Cybertec | https://www.cybertec-postgresql.com