explain.depesz.com

PostgreSQL's explain analyze made readable

Result: y31S

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 4,396.550 ↑ 1.0 1 1

Limit (cost=2,661,000.48..2,661,000.50 rows=1 width=32) (actual time=4,396.549..4,396.550 rows=1 loops=1)

2.          

Initplan (for Limit)

3. 0.015 4,396.516 ↓ 2.3 84 1

Subquery Scan on x (cost=2,660,999.13..2,661,000.48 rows=36 width=32) (actual time=4,396.429..4,396.516 rows=84 loops=1)

4. 0.107 4,396.501 ↓ 2.3 84 1

WindowAgg (cost=2,660,999.13..2,660,999.85 rows=36 width=58) (actual time=4,396.426..4,396.501 rows=84 loops=1)

5. 0.042 4,396.394 ↓ 2.3 84 1

Sort (cost=2,660,999.13..2,660,999.22 rows=36 width=49) (actual time=4,396.390..4,396.394 rows=84 loops=1)

  • Sort Key: transaction_master_p_202001.txn_no
  • Sort Method: quicksort Memory: 31kB
6. 28.745 4,396.352 ↓ 2.3 84 1

Nested Loop Left Join (cost=0.98..2,660,998.20 rows=36 width=49) (actual time=4,347.208..4,396.352 rows=84 loops=1)

  • Join Filter: ((transaction_master_p_202001.txn_no)::text = (r.txn_id)::text)
  • Rows Removed by Join Filter: 430584
7. 5.823 5.823 ↓ 2.3 84 1

Index Scan using reconcile_txn_stock_txn_id_idx on reconcile_txn_stock1 r (cost=0.42..4,902.40 rows=36 width=27) (actual time=0.693..5.823 rows=84 loops=1)

  • Index Cond: ((random_number)::text = 'QGhjZGdpT2piNUNv'::text)
  • Filter: ((type)::text = 'Txn'::text)
  • Rows Removed by Filter: 84
8. 17.606 4,361.784 ↓ 1.1 5,126 84

Materialize (cost=0.56..2,653,495.38 rows=4,838 width=22) (actual time=0.079..51.926 rows=5,126 loops=84)

9. 0.445 4,344.178 ↓ 1.1 5,126 1

Append (cost=0.56..2,653,471.19 rows=4,838 width=22) (actual time=6.608..4,344.178 rows=5,126 loops=1)

10. 1,696.762 1,696.762 ↑ 1.0 1,839 1

Index Scan using transaction_master_p_202001_txndatetime2_ard_no_idx on transaction_master_p_202001 (cost=0.56..1,048,995.58 rows=1,875 width=22) (actual time=6.607..1,696.762 rows=1,839 loops=1)

  • Index Cond: ((txndatetime2 >= '2020-01-01 00:00:00'::timestamp without time zone) AND (txndatetime2 <= '2020-03-21 00:00:00'::timestamp without time zone) AND ((ard_no)::text = '4633'::text))
11. 1,604.116 1,604.116 ↓ 1.0 1,886 1

Index Scan using transaction_master_p_202002_txndatetime2_ard_no_idx on transaction_master_p_202002 (cost=0.56..972,631.94 rows=1,816 width=22) (actual time=51.914..1,604.116 rows=1,886 loops=1)

  • Index Cond: ((txndatetime2 >= '2020-01-01 00:00:00'::timestamp without time zone) AND (txndatetime2 <= '2020-03-21 00:00:00'::timestamp without time zone) AND ((ard_no)::text = '4633'::text))
12. 1,042.855 1,042.855 ↓ 1.2 1,401 1

Index Scan using transaction_master_p_202003_txndatetime2_ard_no_idx on transaction_master_p_202003 (cost=0.56..631,819.48 rows=1,147 width=22) (actual time=39.697..1,042.855 rows=1,401 loops=1)

  • Index Cond: ((txndatetime2 >= '2020-01-01 00:00:00'::timestamp without time zone) AND (txndatetime2 <= '2020-03-21 00:00:00'::timestamp without time zone) AND ((ard_no)::text = '4633'::text))
13. 4,396.549 4,396.549 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=4,396.549..4,396.549 rows=1 loops=1)

Planning time : 4.476 ms
Execution time : 4,397.257 ms