Skip to content

Instantly share code, notes, and snippets.

@databasetech0073

databasetech0073/gist:6688701431dc4bf4eaab8d345c1dc65f Secret

Last active January 31, 2026 13:17
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save databasetech0073/6688701431dc4bf4eaab8d345c1dc65f to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save databasetech0073/6688701431dc4bf4eaab8d345c1dc65f to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
******** QUERY *************
EXPLAIN (ANALYZE, VERBOSE OFF, BUFFERS, COSTS, TIMING, SUMMARY, WAL, SETTINGS)
WITH filtered_entity AS (
SELECT ent_id
FROM APP_schema.ent e
WHERE ent_id = ANY ( '{1001,1002,1003,1004,1005,1006,1007,1008,1009,1010,1011,1012}')
UNION
SELECT ent_id
FROM APP_schema.ent e
WHERE ent_group_id IN (2001,2002,2003,2004,2005)
AND NOT EXISTS (
SELECT 1
FROM APP_schema.ent e2
WHERE e2.ent_group_id = e.ent_group_id
AND e2.ent_id = ANY ( '{1001,1002,1003,1004,1005,1006,1007,1008,1009,1010,1011,1012}')
)
),
filtered_txns AS (
SELECT
c.txn_id, c.ent_id, c.tran_date
FROM APP_schema.txn_tbl c
JOIN APP_schema.txn_decision cd
ON cd.txn_id = c.txn_id
AND cd.is_prnt = 'Y'
AND cd.txn_status NOT IN ('STATUS_A','STATUS_B')
WHERE c.txn_type IN ('TYPE1','TYPE2','TYPE3')
AND c.ent_id IN (SELECT ent_id FROM filtered_entity)
),
limited_txns AS (
SELECT *
FROM filtered_txns
ORDER BY tran_date
LIMIT 1000
),
ckpt_flg AS (
SELECT ck.txn_id,
MAX(txn WHEN ck.ckpt_name = 'FLAG_A' THEN 'Y' END) AS RCL_flg,
MAX(txn WHEN ck.ckpt_name = 'FLAG_B' THEN 'Y' END) AS d_rcv_flg
FROM APP_schema.txn_checkpoint ck
GROUP BY ck.txn_id
)
SELECT
c.txn_id,
c.ent_id,
e.ent_name,
c.acq_ref,
c.txn_type,
c.tran_date,
c.pmthd,
pm.pmt_desc,
c.txn_amt,
cur.crnc_code AS txn_crnc,
c.reason_cd,
r.reason_desc,
r.action_desc,
r.doc_req_desc,
c.create_ts,
cs.status_desc,
cd.decision_desc,
s.order_ref,
...................
...................
FROM limited_txns lc
JOIN APP_schema.txn_tbl c ON c.txn_id = lc.txn_id
JOIN APP_schema.txn_decision cd ON cd.txn_id = c.txn_id
AND cd.is_prnt = 'Y' AND cd.txn_status NOT IN ('STATUS_A','STATUS_B')
JOIN APP_schema.ent e ON e.ent_id = c.ent_id
JOIN APP_schema.ent_group eg ON eg.ent_group_id = e.ent_group_id
JOIN APP_schema.pmthd pm ON pm.pmtcd = c.pmthd
LEFT JOIN APP_schema.reason r ON r.reason_code = c.reason_cd AND r.pmthd = c.pmthd
JOIN APP_schema.txn_status cs ON cs.status_code = cd.txn_status
LEFT JOIN APP_schema.user_tbl u ON u.user_id = cd.assigned_user
JOIN APP_schema.txn_sale s ON s.txn_id = c.txn_id
LEFT JOIN APP_schema.txn_ath a ON a.txn_id = c.txn_id
JOIN APP_schema.crnc cur ON cur.code = c.txn_crnc
JOIN APP_schema.crnc cur2 ON cur2.code = s.txn_crnc
JOIN APP_schema.crnc cur3 ON cur3.code = a.txn_crnc
LEFT JOIN ckpt_flg cf ON cf.txn_id = c.txn_id
ORDER BY lc.tran_date;
************ Query Plan *****************
Sort (cost=73635.22..73637.13 rows=767 width=1284) (actual time=1044.451..1053.303 rows=933 loops=1)
Sort Key: c_1.tran_date
Sort Method: quicksort Memory: 939kB
Buffers: shared hit=284166, temp read=1225 written=1583
I/O Timings: temp read=2.021 write=2.422
-> Hash Join (cost=62386.89..73598.46 rows=767 width=1284) (actual time=1000.379..1051.610 rows=933 loops=1)
Hash Cond: ((s.txn_crnc)::text = (cur2.code)::text)
Buffers: shared hit=284166, temp read=1225 written=1583
I/O Timings: temp read=2.021 write=2.422
-> Hash Join (cost=62379.63..73570.12 rows=767 width=1161) (actual time=1000.272..1049.759 rows=933 loops=1)
Hash Cond: ((c.txn_crnc)::text = (cur.code)::text)
Buffers: shared hit=284163, temp read=1225 written=1583
I/O Timings: temp read=2.021 write=2.422
-> Hash Left Join (cost=62372.38..73551.36 rows=767 width=1157) (actual time=1000.194..1049.239 rows=933 loops=1)
Hash Cond: ((cd.assigned_user)::text = (u.user_id)::text)
Buffers: shared hit=284160, temp read=1225 written=1583
I/O Timings: temp read=2.021 write=2.422
-> Hash Join (cost=62367.20..73544.17 rows=767 width=1143) (actual time=1000.108..1048.840 rows=933 loops=1)
Hash Cond: ((a.txn_crnc)::text = (cur3.code)::text)
Buffers: shared hit=284157, temp read=1225 written=1583
I/O Timings: temp read=2.021 write=2.422
-> Hash Left Join (cost=62359.95..73525.24 rows=800 width=1142) (actual time=1000.025..1048.273 rows=933 loops=1)
Hash Cond: (c.txn_id = ck.txn_id)
Buffers: shared hit=284154, temp read=1225 written=1583
I/O Timings: temp read=2.021 write=2.422
-> Nested Loop (cost=61344.30..72507.50 rows=800 width=1078) (actual time=979.692..1027.397 rows=933 loops=1)
Buffers: shared hit=283961, temp read=1225 written=1583
I/O Timings: temp read=2.021 write=2.422
-> Nested Loop (cost=61343.87..72085.01 rows=800 width=1040) (actual time=979.677..1023.984 rows=933 loops=1)
Buffers: shared hit=280229, temp read=1225 written=1583
I/O Timings: temp read=2.021 write=2.422
-> Nested Loop (cost=61343.44..71639.08 rows=834 width=1002) (actual time=767.279..1020.178 rows=1000 loops=1)
Buffers: shared hit=276296, temp read=1225 written=1583
I/O Timings: temp read=2.021 write=2.422
-> Nested Loop Left Join (cost=61343.29..71615.72 rows=834 width=995) (actual time=767.262..1019.420 rows=1000 loops=1)
Buffers: shared hit=276288, temp read=1225 written=1583
I/O Timings: temp read=2.021 write=2.422
-> Nested Loop (cost=61343.00..71478.41 rows=834 width=210) (actual time=767.239..1018.351 rows=1000 loops=1)
Buffers: shared hit=276252, temp read=1225 written=1583
I/O Timings: temp read=2.021 write=2.422
-> Nested Loop (cost=61342.85..71456.26 rows=834 width=195) (actual time=767.222..1017.500 rows=1000 loops=1)
Buffers: shared hit=276238, temp read=1225 written=1583
I/O Timings: temp read=2.021 write=2.422
-> Nested Loop (cost=61342.43..70861.41 rows=834 width=175) (actual time=767.207..1013.124 rows=1000 loops=1)
Buffers: shared hit=272238, temp read=1225 written=1583
I/O Timings: temp read=2.021 write=2.422
-> Nested Loop (cost=61341.99..70769.77 rows=834 width=148) (actual time=767.179..1012.048 rows=1000 loops=1)
Join Filter: (c.txn_id = c_1.txn_id)
Buffers: shared hit=272194, temp read=1225 written=1583
I/O Timings: temp read=2.021 write=2.422
-> Nested Loop (cost=61341.56..70243.75 rows=899 width=51) (actual time=767.161..1008.015 rows=1000 loops=1)
Buffers: shared hit=268194, temp read=1225 written=1583
I/O Timings: temp read=2.021 write=2.422
-> Limit (cost=61341.13..62179.75 rows=1000 width=20) (actual time=767.133..1004.888 rows=1000 loops=1)
Buffers: shared hit=264194, temp read=1225 written=1583
I/O Timings: temp read=2.021 write=2.422
-> Nested Loop (cost=61341.13..349057.54 rows=343085 width=20) (actual time=767.132..1004.735 rows=1000 loops=1)
Buffers: shared hit=264194, temp read=1225 written=1583
I/O Timings: temp read=2.021 write=2.422
-> Gather Merge (cost=61340.71..109240.57 rows=411276 width=12) (actual time=762.528..792.547 rows=57848 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=32803, temp read=1225 written=1583
I/O Timings: temp read=2.021 write=2.422
-> Sort (cost=60340.68..60769.10 rows=171365 width=12) (actual time=748.456..751.389 rows=20297 loops=3)
Sort Key: c_1.tran_date
Sort Method: external merge Disk: 3400kB
Buffers: shared hit=32803, temp read=1225 written=1583
I/O Timings: temp read=2.021 write=2.422
Worker 0: Sort Method: external merge Disk: 3488kB
Worker 1: Sort Method: external merge Disk: 5720kB
-> Hash Join (cost=328.52..42513.81 rows=171365 width=12) (actual time=0.684..637.971 rows=194538 loops=3)
Hash Cond: ((c_1.ent_id)::numeric = e_1.ent_id)
Buffers: shared hit=32787
-> Parallel Seq Scan on txn_tbl c_1 (cost=0.00..40524.08 rows=590913 width=20) (actual time=0.017..344.552 rows=472901 loops=3)
Filter: ((txn_type)::text = ANY ('{TYPE1,TYPE2,TYPE3}'::text[]))
Rows Removed by Filter: 565
Buffers: shared hit=32392
-> Hash (cost=327.80..327.80 rows=58 width=16) (actual time=0.345..0.351 rows=12 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=375
-> Unique (cost=266.10..327.80 rows=58 width=16) (actual time=0.288..0.344 rows=12 loops=3)
Buffers: shared hit=375
-> Merge Append (cost=266.10..327.65 rows=58 width=16) (actual time=0.288..0.339 rows=12 loops=3)
Sort Key: e_1.ent_id
Buffers: shared hit=375
-> Index Only Scan using ent_pk on ent e_1 (cost=0.42..61.29 rows=12 width=7) (actual time=0.032..0.077 rows=12 loops=3)
Index Cond: (ent_id = ANY ('{1001,1002,1003,1004,1005,1006,1007,1008,1009,1010,1011,1012}'::numeric[]))
Heap Fetches: 3
-> Sort (cost=265.66..265.78 rows=46 width=7) (actual time=0.255..0.258 rows=0 loops=3)
Sort Key: e_2.ent_id
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=268
Worker 0: Sort Method: quicksort Memory: 25kB
Worker 1: Sort Method: quicksort Memory: 25kB
-> Hash Anti Join (cost=83.92..264.39 rows=46 width=7) (actual time=0.244..0.247 rows=0 loops=3)
Hash Cond: (e_2.ent_group_id = e2.ent_group_id)
Buffers: shared hit=268
-> Bitmap Heap Scan on ent e_2 (cost=22.47..202.35 rows=47 width=14) (actual time=0.056..0.104 rows=53 loops=3)
Recheck Cond: (ent_group_id = ANY ('{2001,2002,2003,2004,2005}'::numeric[]))
Heap Blocks: exact=38
Buffers: shared hit=161
-> Bitmap Index Scan on ent_ix1 (cost=0.00..22.46 rows=47 width=0) (actual time=0.038..0.038 rows=53 loops=3)
Index Cond: (ent_group_id = ANY ('{2001,2002,2003,2004,2005}'::numeric[]))
Buffers: shared hit=47
-> Hash (cost=61.30..61.30 rows=12 width=7) (actual time=0.108..0.109 rows=12 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=107
-> Index Only Scan using ent_ix2 on ent e2 (cost=0.42..61.30 rows=12 width=7) (actual time=0.048..0.095 rows=12 loops=3)
Index Cond: (ent_id = ANY ('{1001,1002,1003,1004,1005,1006,1007,1008,1009,1010,1011,1012}'::numeric[]))
Heap Fetches: 3
-> Index Scan using txn_decision_pk on txn_decision cd_1 (cost=0.43..0.58 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=57848)
Index Cond: (txn_id = c_1.txn_id)
Filter: (((txn_status)::text <> ALL ('{STATUS_A,STATUS_B}'::text[])) AND ((is_prnt)::text = 'Y'::text))
Rows Removed by Filter: 1
Buffers: shared hit=231391
-> Index Scan using txn_decision_pk on txn_decision cd (cost=0.43..8.05 rows=1 width=39) (actual time=0.002..0.002 rows=1 loops=1000)
Index Cond: (txn_id = c_1.txn_id)
Filter: (((txn_status)::text <> ALL ('{STATUS_A,STATUS_B}'::text[])) AND ((is_prnt)::text = 'Y'::text))
Buffers: shared hit=4000
-> Index Scan using txn_pk on txn_tbl c (cost=0.43..0.57 rows=1 width=97) (actual time=0.003..0.003 rows=1 loops=1000)
Index Cond: (txn_id = cd.txn_id)
Buffers: shared hit=4000
-> Memoize (cost=0.44..0.51 rows=1 width=34) (actual time=0.001..0.001 rows=1 loops=1000)
Cache Key: c.ent_id
Cache Mode: logical
Hits: 989 Misses: 11 Evictions: 0 Overflows: 0 Memory Usage: 2kB
Buffers: shared hit=44
-> Index Scan using ent_pk on ent e (cost=0.43..0.50 rows=1 width=34) (actual time=0.008..0.008 rows=1 loops=11)
Index Cond: (ent_id = (c.ent_id)::numeric)
Buffers: shared hit=44
-> Index Scan using ent_group_pk on ent_group eg (cost=0.43..0.71 rows=1 width=27) (actual time=0.003..0.003 rows=1 loops=1000)
Index Cond: (ent_group_id = e.ent_group_id)
Buffers: shared hit=4000
-> Memoize (cost=0.15..0.17 rows=1 width=18) (actual time=0.000..0.000 rows=1 loops=1000)
Cache Key: c.pmthd
Cache Mode: logical
Hits: 993 Misses: 7 Evictions: 0 Overflows: 0 Memory Usage: 1kB
Buffers: shared hit=14
-> Index Scan using pmthd_pk on pmthd pm (cost=0.14..0.16 rows=1 width=18) (actual time=0.005..0.005 rows=1 loops=7)
Index Cond: ((pmtcd)::text = (c.pmthd)::text)
Buffers: shared hit=14
-> Memoize (cost=0.29..0.31 rows=1 width=788) (actual time=0.001..0.001 rows=1 loops=1000)
Cache Key: c.reason_cd, c.pmthd
Cache Mode: logical
Hits: 987 Misses: 13 Evictions: 0 Overflows: 0 Memory Usage: 14kB
Buffers: shared hit=36
-> Index Scan using reason_pk on reason r (cost=0.28..0.30 rows=1 width=788) (actual time=0.007..0.007 rows=1 loops=13)
Index Cond: (((pmthd)::text = (c.pmthd)::text) AND ((reason_code)::text = (c.reason_cd)::text))
Buffers: shared hit=36
-> Memoize (cost=0.15..0.17 rows=1 width=33) (actual time=0.000..0.000 rows=1 loops=1000)
Cache Key: cd.txn_status
Cache Mode: logical
Hits: 996 Misses: 4 Evictions: 0 Overflows: 0 Memory Usage: 1kB
Buffers: shared hit=8
-> Index Scan using txn_status_pk on txn_status cs (cost=0.14..0.16 rows=1 width=33) (actual time=0.007..0.007 rows=1 loops=4)
Index Cond: ((status_code)::text = (cd.txn_status)::text)
Buffers: shared hit=8
-> Index Scan using txn_sale_pk on txn_sale s (cost=0.43..0.53 rows=1 width=38) (actual time=0.003..0.003 rows=1 loops=1000)
Index Cond: (txn_id = c.txn_id)
Buffers: shared hit=3933
-> Index Scan using txn_ath_pk on txn_ath a (cost=0.43..0.53 rows=1 width=70) (actual time=0.002..0.002 rows=1 loops=933)
Index Cond: (txn_id = c.txn_id)
Buffers: shared hit=3732
-> Hash (cost=829.60..829.60 rows=14884 width=72) (actual time=20.304..20.306 rows=14913 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 732kB
Buffers: shared hit=193
-> Subquery Scan on ck (cost=531.92..829.60 rows=14884 width=72) (actual time=11.333..17.344 rows=14913 loops=1)
Buffers: shared hit=193
-> HashAggregate (cost=531.92..680.76 rows=14884 width=72) (actual time=11.332..15.507 rows=14913 loops=1)
Group Key: ck_1.txn_id
Batches: 1 Memory Usage: 2065kB
Buffers: shared hit=193
-> Seq Scan on txn_checkpoint ck_1 (cost=0.00..343.63 rows=15063 width=21) (actual time=0.011..3.395 rows=15092 loops=1)
Buffers: shared hit=193
-> Hash (cost=4.89..4.89 rows=189 width=8) (actual time=0.066..0.067 rows=189 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 16kB
Buffers: shared hit=3
-> Seq Scan on crnc cur3 (cost=0.00..4.89 rows=189 width=8) (actual time=0.002..0.026 rows=189 loops=1)
Buffers: shared hit=3
-> Hash (cost=3.97..3.97 rows=97 width=49) (actual time=0.069..0.069 rows=97 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 16kB
Buffers: shared hit=3
-> Seq Scan on user_tbl u (cost=0.00..3.97 rows=97 width=49) (actual time=0.018..0.040 rows=97 loops=1)
Buffers: shared hit=3
-> Hash (cost=4.89..4.89 rows=189 width=8) (actual time=0.066..0.067 rows=189 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 16kB
Buffers: shared hit=3
-> Seq Scan on crnc cur (cost=0.00..4.89 rows=189 width=8) (actual time=0.003..0.027 rows=189 loops=1)
Buffers: shared hit=3
-> Hash (cost=4.89..4.89 rows=189 width=8) (actual time=0.085..0.085 rows=189 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 16kB
Buffers: shared hit=3
-> Seq Scan on crnc cur2 (cost=0.00..4.89 rows=189 width=8) (actual time=0.015..0.042 rows=189 loops=1)
Buffers: shared hit=3
Settings: maintenance_io_concrnc = '1', effective_cache_size = '10475104kB'
Planning:
Buffers: shared hit=217
Planning Time: 13.936 ms
Execution Time: 1056.376 ms
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment