Re: explain

On Fri, 2025-04-25 at 12:36 +1200, David Rowley wrote: > On Fri, 25 Apr 2025 at 03:06, Laurenz Albe wrote: > > On Fri, 2025-04-25 at 01:41 +1200, David Rowley wrote: > > > The 79.3 seconds is the total time spent doing reads for all parallel > > > workers. 52.6 seconds is the wall clock time elaps

Re: explain

On Fri, 25 Apr 2025 at 03:06, Laurenz Albe wrote: > > On Fri, 2025-04-25 at 01:41 +1200, David Rowley wrote: > > The 79.3 seconds is the total time spent doing reads for all parallel > > workers. 52.6 seconds is the wall clock time elapsed to execute the > > query. > > But wouldn't it read "loops=

Re: explain

On Fri, 2025-04-25 at 01:41 +1200, David Rowley wrote: > On Fri, 25 Apr 2025 at 01:32, Marc Millas wrote: > > Nested Loop Left Join (cost=941400.77..966327.57 rows=3 width=653) (actual > > time=52655.694..62533.811 rows=346 loops=1) > > Buffers: shared hit=10068265 read=396705 dirtied=1858 wri

Re: explain

Thanks ! Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Thu, Apr 24, 2025 at 3:41 PM David Rowley wrote: > On Fri, 25 Apr 2025 at 01:32, Marc Millas wrote: > > Nested Loop Left Join (cost=941400.77..966327.57 rows=3 width=653) > (actual time=52655.694..62533.811 rows=346 loops=

Re: explain

On Fri, 25 Apr 2025 at 01:32, Marc Millas wrote: > Nested Loop Left Join (cost=941400.77..966327.57 rows=3 width=653) (actual > time=52655.694..62533.811 rows=346 loops=1) > Buffers: shared hit=10068265 read=396705 dirtied=1858 written=218, temp > read=429687 written=115187 > I/O Timings: r

Re: explain vs auto_explain

On Sat, 19 Oct 2024 at 22:55, David G. Johnston wrote: > > With auto_explain you end up producing multiple individual explains, one > for each top-level query being executed. Each one produced by auto_explain > which hooks into the point where a top-level query begins execution. > Running explai

Re: explain vs auto_explain

On Sat, 19 Oct 2024 at 23:48, Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > > > ok, it makes sense for the reason of having auto_explain. but maybe i did > ask correctly, > why do we not have the extended flags in auto_explain , in , explain wrt > nested_statements, and triggers ...

Re: explain vs auto_explain

On Sat, Oct 19, 2024 at 10:43 AM Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > i tried to check the code for auto_explain , there is nothing that helps > understand why it was provided as a separate . > Probably because output to log was easier than reworking the internals to make o

Re: explain vs auto_explain

"David G. Johnston" writes: > On Sat, Oct 19, 2024 at 10:43 AM Vijaykumar Jain < > vijaykumarjain.git...@gmail.com> wrote: >> i tried to check the code for auto_explain , there is nothing that helps >> understand why it was provided as a separate . > Probably because output to log was easier than

Re: explain vs auto_explain

On Sat, 19 Oct 2024 at 23:31, Tom Lane wrote: > "David G. Johnston" writes: > > On Sat, Oct 19, 2024 at 10:43 AM Vijaykumar Jain < > > vijaykumarjain.git...@gmail.com> wrote: > >> i tried to check the code for auto_explain , there is nothing that helps > >> understand why it was provided as a se

Re: explain vs auto_explain

On Sat, Oct 19, 2024 at 10:05 AM Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > Why does auto_explain have extended flags to support nested statements and > triggers whereas explain does not. > the objects of concern have the same ownership and access, so it does not > even sound like

Re: Explain and filter over subplans

Thank you very much for your quick answer and patch! I tested and this is exactly what I was looking for :-) Best Chantal Le 18/01/2024 à 18:55, Tom Lane a écrit : Chantal Keller writes: I would like "explain" to output formulas for filtering over subplans. Is it possible? No, and that'

Re: Explain and filter over subplans

Chantal Keller writes: > I would like "explain" to output formulas for filtering over > subplans. Is it possible? No, and that's been a to-do item for a long time. Currently, EXPLAIN just ignores the "testexpr" field of SubPlan nodes, which is what you are after. We could print it, if we could

Re: EXPLAIN statement can also apply to SELECT INTO.

On Monday, August 21, 2023, jian he wrote: > hi. > > https://www.postgresql.org/docs/current/sql-explain.html > >>> > Any SELECT, INSERT, UPDATE, DELETE, MERGE, VALUES, EXECUTE, DECLARE, > CREATE TABLE AS, or CREATE MATERIALIZED VIEW AS statement, whose > execution plan you wish to see. > >>> > >

Re: EXPLAIN and FK references?

On Thu, Jan 12, 2023 at 01:33:56AM -0600, Ron wrote: > On 1/12/23 01:11, Tom Lane wrote: > > Ron writes: > > > On 1/12/23 00:07, Tom Lane wrote: > > > > No, not directly, but you could look at EXPLAIN ANALYZE to see which > > > > of the RI triggers is eating the time. > > > Good to know, but even

Re: EXPLAIN and FK references?

On 1/12/23 01:11, Tom Lane wrote: Ron writes: On 1/12/23 00:07, Tom Lane wrote: No, not directly, but you could look at EXPLAIN ANALYZE to see which of the RI triggers is eating the time. Good to know, but even deleting one day of data (90,000 rows using an index scan on the date field) takes

Re: EXPLAIN and FK references?

Ron writes: > On 1/12/23 00:07, Tom Lane wrote: >> No, not directly, but you could look at EXPLAIN ANALYZE to see which >> of the RI triggers is eating the time. > Good to know, but even deleting one day of data (90,000 rows using an index > scan on the date field) takes forever. So delete a re

Re: EXPLAIN and FK references?

On 1/12/23 00:07, Tom Lane wrote: Ron writes: Deletes are slow in one table with many indices and FK references. That's not surprising, but it's *VERY* slow, and I'm trying to figure out why. Is there any EXPLAIN option which shows what "query plans" Pg is using when checking FK references (ind

Re: EXPLAIN and FK references?

On Wed, Jan 11, 2023 at 10:13:11PM -0800, Christophe Pettus wrote: > > Also, IIRC, the SELECTs generated to do foreign key checks do appear in > pg_stat_statements, so that might provide a guide to ones that are consuming > an unusually large amount of resources. Yes, but you need to have pg_stat_

Re: EXPLAIN and FK references?

> On Jan 11, 2023, at 22:07, Tom Lane wrote: > No, not directly, but you could look at EXPLAIN ANALYZE to see which > of the RI triggers is eating the time. It's not going to be hard to > figure out which one(s) are using indexed plans and which are not. Also, IIRC, the SELECTs generated to d

Re: EXPLAIN and FK references?

Ron writes: > Deletes are slow in one table with many indices and FK references. That's > not surprising, but it's *VERY* slow, and I'm trying to figure out why. > Is there any EXPLAIN option which shows what "query plans" Pg is using when > checking FK references (index scan, seq scan, etc) du

Re: EXPLAIN ANALYZE does not return accurate execution times

Mark Mizzi writes: > So to confirm, EXPLAIN ANALYZE does not detoast rows? Not values that would have been transmitted to the client, no. regards, tom lane

Re: EXPLAIN ANALYZE does not return accurate execution times

Hi, thanks for your reply. So to confirm, EXPLAIN ANALYZE does not detoast rows? The original goal of these queries was to see the effect of fetching from toast tables on query performance. On Thu, 27 Oct 2022 at 15:43, Tom Lane wrote: > Mark Mizzi writes: > > When I run > > > EXPLAIN ANALYZE S

Re: EXPLAIN ANALYZE does not return accurate execution times

Mark Mizzi writes: > When I run > EXPLAIN ANALYZE SELECT * FROM unary; > I get the following result: > Seq Scan on unary (cost=0.00..1637.01 rows=11 width=18) (actual > time=0.009..6.667 rows=11 loops=1) > Planning Time: 0.105 ms > Execution Time: 8.565 ms > On the other hand, the

Re: EXPLAIN ANALYZE does not return accurate execution times

Hi, On Thu, Oct 27, 2022 at 03:28:14PM +0200, Mark Mizzi wrote: > > EXPLAIN ANALYZE SELECT * FROM unary; > > I get the following result: > > Seq Scan on unary (cost=0.00..1637.01 rows=11 width=18) (actual > time=0.009..6.667 rows=11 loops=1) > Planning Time: 0.105 ms > Execution Time:

Re: Explain returns different number of rows

Thanks for the clarification, Peter. On Sat, Oct 22, 2022, 05:32 Peter J. Holzer wrote: > On 2022-10-20 09:56:23 -0700, Christophe Pettus wrote: > > On Oct 20, 2022, at 09:52, Vince McMahon > wrote: > > > The number of rows are different. > > > > This isn't unexpected. EXPLAIN does not actua

Re: Explain returns different number of rows

On 2022-10-20 09:56:23 -0700, Christophe Pettus wrote: > On Oct 20, 2022, at 09:52, Vince McMahon > wrote: > > The number of rows are different. > > This isn't unexpected. EXPLAIN does not actually run the query and > determine how many rows are returned; it calculates an estimate based > on t

Re: Explain returns different number of rows

> On Oct 20, 2022, at 09:52, Vince McMahon > wrote: > The number of rows are different. This isn't unexpected. EXPLAIN does not actually run the query and determine how many rows are returned; it calculates an estimate based on the current system statistics, which vary constantly dependin

Re: EXPLAIN with anonymous DO block?

Good day! There's a query inside a DO block which -- because it's parameterized -- I'd rather analyze while it's in the FOR loop of a DO block, instead of pulling it out and hard-coding the parameters. Is this possible? No. Why not to use auto_explain module? postgres=# LOAD 'auto_explain'; L

Re: EXPLAIN with anonymous DO block?

On Thu, Jul 1, 2021 at 9:22 AM Michael Lewis wrote: > It sounds like you are wanting to run 'explain analyze [query]' inside a > loop inside a DO block. That isn't possible as far as I know, but > auto_explain and log_nested_statements should be able to let you profile > the whole thing and perha

Re: EXPLAIN with anonymous DO block?

Ron writes: > There's a query inside a DO block which -- because it's parameterized -- I'd > rather analyze while it's in the FOR loop of a DO block, instead of pulling > it out and hard-coding the parameters. > Is this possible? No. The thing to do to duplicate the behavior of a plpgsql quer

Re: EXPLAIN with anonymous DO block?

It sounds like you are wanting to run 'explain analyze [query]' inside a loop inside a DO block. That isn't possible as far as I know, but auto_explain and log_nested_statements should be able to let you profile the whole thing and perhaps you can pick out the part you want from the logs.

Re: EXPLAIN (ANALYZE, BUFFERS) - puzzling numbers for a simple query.

> Laurenz Albe writes: > > "generate_series" has a support function from v12 on: > True, but I don't think it can do anything with non-constant inputs, > as we have in the OP's case. As confirmed by this: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=d0b70717faca3b094be8b4a096758b94 Plan: =

Re: EXPLAIN (ANALYZE, BUFFERS) - puzzling numbers for a simple query.

Laurenz Albe writes: > On Sat, 2021-06-05 at 00:12 +1200, David Rowley wrote: >> As for the call to generate_series, you're not likely to ever get any >> great estimation from that. > "generate_series" has a support function from v12 on: True, but I don't think it can do anything with non-consta

Re: EXPLAIN (ANALYZE, BUFFERS) - puzzling numbers for a simple query.

On Sat, 5 Jun 2021 at 00:55, Laurenz Albe wrote: > > On Sat, 2021-06-05 at 00:12 +1200, David Rowley wrote: > > As for the call to generate_series, you're not likely to ever get any > > great estimation from that. The number of rows returned by a call to > > that particular function are just what

Re: EXPLAIN (ANALYZE, BUFFERS) - puzzling numbers for a simple query.

On Sat, 2021-06-05 at 00:12 +1200, David Rowley wrote: > As for the call to generate_series, you're not likely to ever get any > great estimation from that. The number of rows returned by a call to > that particular function are just whatever is set in pg_proc.prorows, > in this case, 1000. The o

Re: EXPLAIN (ANALYZE, BUFFERS) - puzzling numbers for a simple query.

On Fri, 4 Jun 2021 at 22:59, Pól Ua Laoínecháin wrote: > Now, we've gone from 1,160,000 to 39,830 rows (progress? :-) ) and a > cost of ~ 1M (compared with 168k for the first query). The estimates are not that meaningful due to a lack of table statistics on the "test" table. If you run ANALYZE o

Re: EXPLAIN (ANALYZE, BUFFERS) - puzzling numbers for a simple query.

how is cost calculated? postgres/costsize.c at master · postgres/postgres (github.com) row estimation PostgreSQL: Documentation: 13: 70.1. Row Estimation Examples

Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )

On Fri, 8 May 2020 at 10:00, David G. Johnston wrote: > > On Thu, May 7, 2020 at 11:07 AM Amarendra Konda wrote: >> >> EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT pa.process_activity_id AS >> pa_process_activity_id FROM process_activity pa WHERE pa.app_id = >> '126502930200650' AND pa.c

Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )

On Thu, May 7, 2020 at 11:07 AM Amarendra Konda wrote: > EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT pa.process_activity_id > AS pa_process_activity_id FROM process_activity pa WHERE pa.app_id = > '126502930200650' AND pa.created > '1970-01-01 00:00:00' AND EXISTS ( > SELECT 1 FROM proce

Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )

"David G. Johnston" writes: > On Thu, May 7, 2020 at 10:49 AM Amarendra Konda > wrote: >> Can you please explain, why it is getting more columns in output, even >> though we have asked for only one column ? >> * Output: pa.process_activity_id, pa.process_activity_type, pa.voice_url, >> pa.process

Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )

On Thu, May 7, 2020 at 10:49 AM Amarendra Konda wrote: > Can you please explain, why it is getting more columns in output, even > though we have asked for only one column ? > > > > * Output: pa.process_activity_id, pa.process_activity_type, pa.voice_url, > pa.process_activity_user_id, pa.app_id,

Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )

Here is my thought on why row is not limiting when joined vs why it is limiting when not joined. When not joined and where clause is having IN, it is using index process_activity_process_instance_id_app_id_created_idx which has columns process_instance_id, created which is in order by and hence

Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )

On 5/7/20 10:49 AM, Amarendra Konda wrote: Hi David, Thanks for the reply.This has optimized number of rows. Yeah, but your execution time has increased an order of magnitude. Not sure if that is what you want. Can you please explain, why it is getting more columns in output, even though

Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )

Hi Virendra, Thanks for your time. Here is the table and index structure * process_activity* Table "public.process_activity" Column |Type | Modifiers +-+-

Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )

Hi David, In earlier reply, Over looked another condition, hence please ignore that one Here is the correct one with all the needed conditions. According to the latest one, exists also not limiting rows from the process_activity table. EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT pa.proce

Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )

Hi David, Thanks for the reply.This has optimized number of rows. Can you please explain, why it is getting more columns in output, even though we have asked for only one column ? EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT pa.process_activity_id AS pa_process_activity_id FROM process_

Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )

Hi Adrian, Thanks for the reply. And i have kept latest execution plans, for various SQL statements ( inner join, sub queries and placing values instead of sub query) . As suggested, tried with INNER JOIN, however result was similar to subquery. Is there any way we can tell the optimiser to proc

Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )

On Thu, May 7, 2020 at 7:40 AM Adrian Klaver wrote: > On 5/7/20 4:19 AM, Amarendra Konda wrote: > > Hi, > > > > PostgreSQL version : PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled > > by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit > > > > We have noticed huge difference interms of execu

Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )

On 5/7/20 4:19 AM, Amarendra Konda wrote: Hi, PostgreSQL version : PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit We have noticed huge difference interms of execution plan ( response time) , When we pass the direct values  Vs  inner qu

Re: Explain says 8 workers planned, only 1 executed

Alastair McKinley writes: > Thanks for solving the mystery. I think this might be a missing point in > section 15.2 in the docs. > I wonder will this ever be improved or should I just write to temporary > tables instead of return query? I just posted a patch to improve that [1], but it's not s

Re: Explain says 8 workers planned, only 1 executed

Sent: 21 March 2020 20:50 To: Alastair McKinley Cc: Adrian Klaver ; pgsql-general@lists.postgresql.org Subject: Re: Explain says 8 workers planned, only 1 executed Unfortunately, return query will never use parallel workers. See: https://stackoverflow.com/q/58079898/895640 and https

Re: Explain says 8 workers planned, only 1 executed

namic queries in plpgsql functions? Does the outer function > need to be parallel safe? > Might a stored proc work better? > > Best regards, > > Alastair > > > -- > *From:* Adrian Klaver > *Sent:* 21 March 2020 17:38 > *To:* Alastair McKinley ; > pgsql-general@lists.postg

Re: Explain says 8 workers planned, only 1 executed

Sent: 21 March 2020 17:38 To: Alastair McKinley ; pgsql-general@lists.postgresql.org Subject: Re: Explain says 8 workers planned, only 1 executed On 3/21/20 10:25 AM, Alastair McKinley wrote: > Hi all, > > I have a long running query that I have tweaked along with config (e.g. > min_

Re: Explain says 8 workers planned, only 1 executed

On 3/21/20 10:25 AM, Alastair McKinley wrote: Hi all, I have a long running query that I have tweaked along with config (e.g. min_parallel_table_scan_size) to execute nicely and very fast in parallel which works as expected executed directly from psql client. The query is then embedded in a

Re: explain plan difference

> > Never, unless you drop and recreate the table. Removing a dropped > column would change the attnums of following columns, which we > can't support because the tableoid+attnum is the only persistent > identifier of a column. > > (From memory, operations like VACUUM FULL and CLUSTER will rewri

Re: explain plan difference

Ravi Krishna writes: >> VACUUM would not change the state of the dropped columns. > When does it change? Never, unless you drop and recreate the table. Removing a dropped column would change the attnums of following columns, which we can't support because the tableoid+attnum is the only persist

Re: explain plan difference

>> Just to confirm, we are talking about tables from where some cols were >> deleted in the past, but >> VACUUM FULL not run on that table, right ? > > VACUUM would not change the state of the dropped columns. > When does it change?

Re: explain plan difference

Ravi Krishna writes: >> On Nov 3, 2019, at 11:03 PM, Steve Baldwin wrote: >> Thanks very much for the explanation Tom !! You are correct - there are >> dropped columns in the original. > Just to confirm, we are talking about tables from where some cols were > deleted in the past, but > VACUU

Re: explain plan difference

> On Nov 3, 2019, at 11:03 PM, Steve Baldwin wrote: > > Thanks very much for the explanation Tom !! You are correct - there are > dropped columns in the original. Just to confirm, we are talking about tables from where some cols were deleted in the past, but VACUUM FULL not run on that ta

Re: explain plan difference

Thanks very much for the explanation Tom !! You are correct - there are dropped columns in the original. Cheers, Steve On Mon, Nov 4, 2019 at 3:01 PM Tom Lane wrote: > Steve Baldwin writes: > > I guess the difference doesn't have a huge bearing (as far as I can tell) > > on the result, but i

Re: explain plan difference

Steve Baldwin writes: > I guess the difference doesn't have a huge bearing (as far as I can tell) > on the result, but it just seems odd that the inner-most 'Output' step > outputs the entire row in the case of the copy and only the required field > in the case of the original table. What triggers

Re: EXPLAIN BUFFERS and I/O timing accounting questions

Hi, On 2019-10-24 16:31:39 -0700, Maciek Sakrejda wrote: > On Thu, Oct 24, 2019 at 2:25 PM Andres Freund wrote: > > Note that the buffer access stats do *not* count the number of distinct > > buffers accessed, but that they purely the number of buffer > > accesses. > > You mean, even within a si

Re: EXPLAIN BUFFERS and I/O timing accounting questions

On Thu, Oct 24, 2019 at 2:25 PM Andres Freund wrote: > Note that the buffer access stats do *not* count the number of distinct > buffers accessed, but that they purely the number of buffer > accesses. You mean, even within a single node? That is, if a node hits a block ten times, that counts as t

Re: EXPLAIN BUFFERS and I/O timing accounting questions

Hi, On 2019-10-21 23:18:32 -0700, Maciek Sakrejda wrote: > I ran across an EXPLAIN plan and had some questions about some of its > details. The BUFFERS docs say > > >The number of blocks shown for an upper-level node includes those used by > all its child nodes. > > I initially assumed this woul

Re: EXPLAIN BUFFERS and I/O timing accounting questions

Also, I noticed that in this plan, the root (again, an Aggregate) has 0 Temp Read Blocks, but two of its children (two of the ModifyTable nodes) have non-zero Temp Read Blocks. Again, this contradicts the documentation, as these costs are stated to be cumulative. Any ideas? Thanks, Maciek

Re: explain analyze cost

> Please do not hijack other threads by replying to a message and > changing> the subject. Just send a new mail to > pgsql-general@lists.postgresql.org, or whatever list you want > to send an> email to. > I am truly sorry and this will not be repeated. I was just lazy. I guess this would break

Re: explain analyze cost

On 2018-12-12 17:37:47 -0500, Ravi Krishna wrote: > I am running explain analyze cost on a SQL which reads from two large > tables (122mil and 37 mil). The query is an UPDATE SQL where we use > derives table in the from clause and then join it back to the table > being updated. > The explain analy

Re: explain analyze cost

On 12/12/2018 04:37 PM, Ravi Krishna wrote: I am running explain analyze cost on a SQL which reads from two large tables (122mil and 37 mil).  The query is an UPDATE SQL where we use derives table in the from clause and then join it back to the table being updated. The explain analyze cost it

Re: EXPLAIN BUFFERS: dirtied

Thanks!

Re: EXPLAIN BUFFERS: dirtied

On 01/29/2018 08:21 PM, Vitaliy Garnashevich wrote: > I've read this article: https://wiki.postgresql.org/wiki/Hint_Bits > > It says: > >> A plain SELECT, count(*), or VACUUM on the entire table will check >> every tuple for visibility and set its hint bits. > > Suppose, a new page was create

Re: EXPLAIN BUFFERS: dirtied

I've read this article: https://wiki.postgresql.org/wiki/Hint_Bits It says: A plain SELECT, count(*), or VACUUM on the entire table will check every tuple for visibility and set its hint bits. Suppose, a new page was created using many INSERTs, and then was written to disk during a checkpoi

Re: EXPLAIN BUFFERS: dirtied

Vitaliy Garnashevich writes: > But what is "dirtied" statistics? When a SELECT query could make pages > dirty? Setting hint bits on recently-committed rows. regards, tom lane