pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
(no subject)
4+ messages / 3 participants
[nested] [flat]

* (no subject)
@ 2024-01-25 17:18 Ron Johnson <ronljohnsonjr@gmail.com>
  2024-01-25 17:29 ` Re: Tom Lane <tgl@sss.pgh.pa.us>
  2024-01-26 08:23 ` Re: Laurenz Albe <laurenz.albe@cybertec.at>
  0 siblings, 2 replies; 4+ messages in thread

From: Ron Johnson @ 2024-01-25 17:18 UTC (permalink / raw)
  To: pgsql-general

PG 14.10 (and 9.6.24, which we're migrating off of).

EXPLAIN SELECT works inside a FOR loop, but only the first line of the
EXPLAIN output is stored.  What's the magic sauce for seeing the whole
EXPLAIN output?

(The purpose is to generate many queries and see how the BIND and SELECT
times change from 9.6.24 to 14.10.)

DO
$$
DECLARE
    r RECORD;
    _v_count BIGINT;
    _v_explain TEXT;
    _v_part_type NUMERIC = 11;
BEGIN
    FOR r IN
        SELECT doc_item_mapping_id
             , process_date
        FROM doc_item_mapping_rp11_y2023m09
        WHERE process_date < '2024-01-20'
        ORDER BY process_date DESC
        LIMIT 10
    LOOP
        RAISE NOTICE '% %', r.doc_item_mapping_id, r.process_date;
        EXPLAIN select count(*) INTO _v_explain
        from cds.V_ALL_LBX_DOC_CHECK_ITEM_MAPPING_EMS
        where (DOC_ITEM_MAPPING_ID= r.doc_item_mapping_id)
          and (ACCOUNT_NUMBER is not null )
          and PARTITION_TYPE= _v_part_type
          and PARTITION_TYPE_SUB_BATCH= _v_part_type
          and PARTITION_TYPE_CDSSUBBATCH= _v_part_type
          and PARTITION_TYPE_TRANSACTION= _v_part_type
          and PARTITION_TYPE_SUB_TRANSACTION= _v_part_type
          and PARTITION_TYPE_PAYMENT_DOC= _v_part_type
          and PARTITION_TYPE_CHECK_DOC_ITEM_MAPPING= _v_part_type
          and PARTITION_TYPE_ITEM_MAPPING= _v_part_type
          and PARTITION_TYPE_CHECK= _v_part_type
          and PARTITION_TYPE_BATCH_ASSOCIATION= _v_part_type
          and PROCESS_DATE_SUB_BATCH= r.process_date
          and PROCESS_DATE_CDSSUBBATCH= r.process_date
          and PROCESS_DATE_TRANSACTION= r.process_date
          and PROCESS_DATE_SUB_TRANSACTION= r.process_date
          and PROCESS_DATE_PAYMENT_DOC= r.process_date
          and PROCESS_DATE_CHECK_DOC_ITEM_MAPPING= r.process_date
          and PROCESS_DATE_ITEM_MAPPING= r.process_date
          and PROCESS_DATE_CHECK=  r.process_date
          and PROCESS_DATE_BATCH_ASSOCIATION= r.process_date;
        RAISE NOTICE '    %', _v_explain;
END LOOP;
END
$$;
NOTICE:  564514534 2024-01-19
NOTICE:      Aggregate  (cost=3476.53..3476.54 rows=1 width=8)
NOTICE:  564514536 2024-01-19
NOTICE:      Aggregate  (cost=3476.53..3476.54 rows=1 width=8)
NOTICE:  564514537 2024-01-19
NOTICE:      Aggregate  (cost=3476.53..3476.54 rows=1 width=8)
NOTICE:  564514539 2024-01-19
NOTICE:      Aggregate  (cost=3476.53..3476.54 rows=1 width=8)
NOTICE:  564514540 2024-01-19
NOTICE:      Aggregate  (cost=3476.53..3476.54 rows=1 width=8)
NOTICE:  564514542 2024-01-19
NOTICE:      Aggregate  (cost=3476.53..3476.54 rows=1 width=8)
NOTICE:  564514543 2024-01-19
NOTICE:      Aggregate  (cost=3476.53..3476.54 rows=1 width=8)
NOTICE:  564514545 2024-01-19
NOTICE:      Aggregate  (cost=3476.53..3476.54 rows=1 width=8)
NOTICE:  564514547 2024-01-19
NOTICE:      Aggregate  (cost=3476.53..3476.54 rows=1 width=8)
NOTICE:  564514549 2024-01-19
NOTICE:      Aggregate  (cost=3476.53..3476.54 rows=1 width=8)

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re:
  2024-01-25 17:18 (no subject) Ron Johnson <ronljohnsonjr@gmail.com>
@ 2024-01-25 17:29 ` Tom Lane <tgl@sss.pgh.pa.us>
  2024-01-25 20:24   ` Re: Ron Johnson <ronljohnsonjr@gmail.com>
  1 sibling, 1 reply; 4+ messages in thread

From: Tom Lane @ 2024-01-25 17:29 UTC (permalink / raw)
  To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: pgsql-general

Ron Johnson <ronljohnsonjr@gmail.com> writes:
> EXPLAIN SELECT works inside a FOR loop, but only the first line of the
> EXPLAIN output is stored.  What's the magic sauce for seeing the whole
> EXPLAIN output?

The usual way is to run a FOR loop over the lines of output.
Quick & dirty example:

regression=# do $$
declare ln text;
begin
  for ln in explain select * from int8_tbl join int4_tbl on q1=f1 loop
    raise notice '%', ln;
  end loop;
end $$;
NOTICE:  Hash Join  (cost=1.11..2.23 rows=5 width=20)
NOTICE:    Hash Cond: (int8_tbl.q1 = int4_tbl.f1)
NOTICE:    ->  Seq Scan on int8_tbl  (cost=0.00..1.05 rows=5 width=16)
NOTICE:    ->  Hash  (cost=1.05..1.05 rows=5 width=4)
NOTICE:          ->  Seq Scan on int4_tbl  (cost=0.00..1.05 rows=5 width=4)
DO

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re:
  2024-01-25 17:18 (no subject) Ron Johnson <ronljohnsonjr@gmail.com>
  2024-01-25 17:29 ` Re: Tom Lane <tgl@sss.pgh.pa.us>
@ 2024-01-25 20:24   ` Ron Johnson <ronljohnsonjr@gmail.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Ron Johnson @ 2024-01-25 20:24 UTC (permalink / raw)
  To: pgsql-general

On Thu, Jan 25, 2024 at 12:29 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Ron Johnson <ronljohnsonjr@gmail.com> writes:
> > EXPLAIN SELECT works inside a FOR loop, but only the first line of the
> > EXPLAIN output is stored.  What's the magic sauce for seeing the whole
> > EXPLAIN output?
>
> The usual way is to run a FOR loop over the lines of output.
> Quick & dirty example:
>
> regression=# do $$
> declare ln text;
> begin
>   for ln in explain select * from int8_tbl join int4_tbl on q1=f1 loop
>     raise notice '%', ln;
>   end loop;
> end $$;
> NOTICE:  Hash Join  (cost=1.11..2.23 rows=5 width=20)
> NOTICE:    Hash Cond: (int8_tbl.q1 = int4_tbl.f1)
> NOTICE:    ->  Seq Scan on int8_tbl  (cost=0.00..1.05 rows=5 width=16)
> NOTICE:    ->  Hash  (cost=1.05..1.05 rows=5 width=4)
> NOTICE:          ->  Seq Scan on int4_tbl  (cost=0.00..1.05 rows=5 width=4)
> DO
>
>
Thanks.

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re:
  2024-01-25 17:18 (no subject) Ron Johnson <ronljohnsonjr@gmail.com>
@ 2024-01-26 08:23 ` Laurenz Albe <laurenz.albe@cybertec.at>
  1 sibling, 0 replies; 4+ messages in thread

From: Laurenz Albe @ 2024-01-26 08:23 UTC (permalink / raw)
  To: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-general

On Thu, 2024-01-25 at 12:18 -0500, Ron Johnson wrote:
> PG 14.10 (and 9.6.24, which we're migrating off of).
> 
> EXPLAIN SELECT works inside a FOR loop, but only the first line of the EXPLAIN
> output is stored.  What's the magic sauce for seeing the whole EXPLAIN output?

DECLARE
   _v_explain jsonb;
BEGIN
   EXECUTE 'EXPLAIN (FORMAT JSON) select ...' INTO _v_explain;
   RAISE NOTICE '%', _v_explain;
END;

Yours,
Laurenz Albe





^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2024-01-26 08:23 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-01-25 17:18  Ron Johnson <ronljohnsonjr@gmail.com>
2024-01-25 17:29 ` Re: Tom Lane <tgl@sss.pgh.pa.us>
2024-01-25 20:24   ` Re: Ron Johnson <ronljohnsonjr@gmail.com>
2024-01-26 08:23 ` Re: Laurenz Albe <laurenz.albe@cybertec.at>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox