- Mailing list pgsql-general
| From | Ron Johnson |
|---|---|
| Subject | |
| Date | |
| Msg-id | CANzqJaAf+h+Fn=-4n__-MxWcZ6Vp-KYe6UvqL930wBSXCkn21g@mail.gmail.com Whole thread Raw |
| Responses |
Re:
Re: |
| List | 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)
$$
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)
pgsql-general by date: