explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Rvc

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 123,042.863 ↓ 25.0 25 1

Limit (cost=27,022.31..27,027.65 rows=1 width=281) (actual time=123,024.886..123,042.863 rows=25 loops=1)

  • Buffers: shared hit=1379331 read=10021 dirtied=4, temp read=156172 written=287941
  • I/O Timings: read=68936.967
2. 1.341 123,042.858 ↓ 25.0 25 1

Subquery Scan on temp1 (cost=27,022.31..27,027.65 rows=1 width=281) (actual time=123,024.884..123,042.858 rows=25 loops=1)

  • Filter: (((temp1.pan_no)::text > '0'::text) AND (temp1.rn = 1))
  • Rows Removed by Filter: 5917
  • Buffers: shared hit=1379331 read=10021 dirtied=4, temp read=156172 written=287941
  • I/O Timings: read=68936.967
3. 1,279.160 123,041.517 ↓ 19.5 5,942 1

Sort (cost=27,022.31..27,023.08 rows=305 width=281) (actual time=123,024.561..123,041.517 rows=5,942 loops=1)

  • Sort Key: (CASE WHEN ((cm.inv_type)::text = ANY ('{02,28}'::text[])) THEN cm.guardian_panno ELSE cm.pan_no END)
  • Sort Method: external merge Disk: 1151864kB
  • Buffers: shared hit=1379331 read=10021 dirtied=4, temp read=156172 written=287941
  • I/O Timings: read=68936.967
4. 224.066 121,762.357 ↓ 125.4 38,234 1

WindowAgg (cost=27,002.10..27,009.73 rows=305 width=281) (actual time=121,081.193..121,762.357 rows=38,234 loops=1)

  • Buffers: shared hit=1379328 read=10021 dirtied=4, temp read=143941 written=143941
  • I/O Timings: read=68936.967
5. 1,730.106 121,538.291 ↓ 125.4 38,234 1

Sort (cost=27,002.10..27,002.86 rows=305 width=191) (actual time=121,081.161..121,538.291 rows=38,234 loops=1)

  • Sort Key: pt.folio_no, cm.first_name
  • Sort Method: external sort Disk: 1151528kB
  • Buffers: shared hit=1379328 read=10021 dirtied=4, temp read=143941 written=143941
  • I/O Timings: read=68936.967
6. 82.363 119,808.185 ↓ 125.4 38,234 1

WindowAgg (cost=130.33..26,989.52 rows=305 width=191) (actual time=828.205..119,808.185 rows=38,234 loops=1)

  • Buffers: shared hit=1379328 read=10021 dirtied=4
  • I/O Timings: read=68936.967
7. 42.798 119,725.822 ↓ 125.4 38,234 1

Nested Loop Left Join (cost=130.33..26,983.42 rows=305 width=159) (actual time=52.696..119,725.822 rows=38,234 loops=1)

  • Buffers: shared hit=1379328 read=10021 dirtied=4
  • I/O Timings: read=68936.967
8. 52.077 119,377.152 ↓ 130.9 38,234 1

Nested Loop Left Join (cost=129.91..25,850.16 rows=292 width=147) (actual time=49.731..119,377.152 rows=38,234 loops=1)

  • Buffers: shared hit=1244537 read=9994 dirtied=1
  • I/O Timings: read=68824.538
9. 22.978 69,110.211 ↓ 93.9 18,024 1

Nested Loop Left Join (cost=30.18..5,924.66 rows=192 width=145) (actual time=25.399..69,110.211 rows=18,024 loops=1)

  • Buffers: shared hit=165849 read=9935 dirtied=1
  • I/O Timings: read=68485.917
10. 26.068 68,979.089 ↓ 93.9 18,024 1

Nested Loop Left Join (cost=29.75..5,176.05 rows=192 width=133) (actual time=25.362..68,979.089 rows=18,024 loops=1)

  • Buffers: shared hit=111777 read=9935 dirtied=1
  • I/O Timings: read=68485.917
11. 34.797 68,736.733 ↓ 93.9 18,024 1

Nested Loop (cost=29.33..4,430.89 rows=192 width=121) (actual time=17.242..68,736.733 rows=18,024 loops=1)

  • Buffers: shared hit=57502 read=9879 dirtied=1
  • I/O Timings: read=68413.380
12. 30.223 68,521.696 ↓ 93.9 18,024 1

Nested Loop (cost=28.91..3,999.62 rows=192 width=130) (actual time=17.160..68,521.696 rows=18,024 loops=1)

  • Join Filter: ((cm.folio_no)::text = (pt.folio_no)::text)
  • Buffers: shared hit=2364 read=9864 dirtied=1
  • I/O Timings: read=68413.269
13. 0.792 142.423 ↓ 3.7 275 1

Nested Loop (cost=28.34..3,143.66 rows=75 width=128) (actual time=6.119..142.423 rows=275 loops=1)

  • Buffers: shared hit=932 read=81 dirtied=1
  • I/O Timings: read=129.843
14. 30.817 30.817 ↑ 1.0 69 1

Index Scan using i190110194246008271677 on r_customer_master cm (cost=0.43..556.50 rows=72 width=114) (actual time=5.959..30.817 rows=69 loops=1)

  • Index Cond: ((folio_no)::text = ANY ('{2287491,2647899,2964605,2994179,3068674,3145550,3150919,3152669,3306336,3311500,3368693,3413733,3938566,4213381,4368789,4564843,4723286,4989421,4989427,4993819,5010780,5012083,5325098,2287488,2287491,2540670,2598593,2647899,2692227,2801542,2904192,2947006,2976890,2980101,2990168,3065074,3103559,3103807,3104317,3115738,3115791,3131368,3146760,3154317,3157462,3162185,3198143,3198177,3215233,3215251,3220178,3220179,3220613,3221016,3256203,3292112,3347711,3413733,3489116,3687387,3688075,3689354,4004877,4365392,4722491,4723285,5012084,5360632,5220918,4306036,5281542,2954663}'::text[]))
  • Filter: (CASE WHEN ((inv_type)::text = ANY ('{02,28}'::text[])) THEN guardian_panno ELSE pan_no END IS NOT NULL)
  • Buffers: shared hit=226 read=53 dirtied=1
  • I/O Timings: read=29.227
15. 101.844 110.814 ↓ 2.0 4 69

Bitmap Heap Scan on r_customer_schemes cs (cost=27.91..35.91 rows=2 width=14) (actual time=0.793..1.606 rows=4 loops=69)

  • Recheck Cond: ((folio_no)::text = (cm.folio_no)::text)
  • Heap Blocks: exact=272
  • Buffers: shared hit=706 read=28
  • I/O Timings: read=100.616
16. 8.970 8.970 ↓ 2.0 4 69

Bitmap Index Scan on i190110195707255924429 (cost=0.00..27.91 rows=2 width=0) (actual time=0.130..0.130 rows=4 loops=69)

  • Index Cond: ((folio_no)::text = (cm.folio_no)::text)
  • Buffers: shared hit=458 read=4
  • I/O Timings: read=0.043
17. 68,349.050 68,349.050 ↓ 16.5 66 275

Index Scan using i190111004045786979738 on r_processed_trxns pt (cost=0.57..11.36 rows=4 width=19) (actual time=12.781..248.542 rows=66 loops=275)

  • Index Cond: (((sch_code)::text = (cs.sch_code)::text) AND ((folio_no)::text = (cs.folio_no)::text))
  • Buffers: shared hit=1432 read=9783
  • I/O Timings: read=68283.425
18. 180.240 180.240 ↑ 1.0 1 18,024

Index Only Scan using i190110193140329189067 on r_broker_master bm (cost=0.41..2.25 rows=1 width=10) (actual time=0.010..0.010 rows=1 loops=18,024)

  • Index Cond: (broker_code = (pt.broker_code)::text)
  • Heap Fetches: 1080
  • Buffers: shared hit=55138 read=15
  • I/O Timings: read=0.111
19. 216.288 216.288 ↓ 0.0 0 18,024

Index Scan using i19011020214988029152 on r_joint_holders jh2 (cost=0.42..3.88 rows=1 width=19) (actual time=0.012..0.012 rows=0 loops=18,024)

  • Index Cond: (((pt.folio_no)::text = (folio_no)::text) AND (joint_no = 2))
  • Buffers: shared hit=54275 read=56
  • I/O Timings: read=72.537
20. 108.144 108.144 ↓ 0.0 0 18,024

Index Scan using i19011020214988029152 on r_joint_holders jh3 (cost=0.42..3.88 rows=1 width=19) (actual time=0.006..0.006 rows=0 loops=18,024)

  • Index Cond: (((pt.folio_no)::text = (folio_no)::text) AND (joint_no = 3))
  • Buffers: shared hit=54072
21. 54.072 50,214.864 ↑ 1.0 2 18,024

Bitmap Heap Scan on r_multiple_bank mb (cost=99.73..103.76 rows=2 width=9) (actual time=2.785..2.786 rows=2 loops=18,024)

  • Recheck Cond: ((pt.folio_no)::text = (folio_no)::text)
  • Heap Blocks: exact=33355
  • Buffers: shared hit=1078688 read=59
  • I/O Timings: read=338.621
22. 50,160.792 50,160.792 ↑ 1.0 2 18,024

Bitmap Index Scan on i190110202255488814780 (cost=0.00..99.73 rows=2 width=0) (actual time=2.783..2.783 rows=2 loops=18,024)

  • Index Cond: ((pt.folio_no)::text = (folio_no)::text)
  • Buffers: shared hit=1045333 read=59
  • I/O Timings: read=338.621
23. 305.872 305.872 ↑ 1.0 1 38,234

Index Scan using i19011020214988029152 on r_joint_holders jh1 (cost=0.42..3.88 rows=1 width=19) (actual time=0.008..0.008 rows=1 loops=38,234)

  • Index Cond: (((pt.folio_no)::text = (folio_no)::text) AND (joint_no = 1))
  • Buffers: shared hit=134791 read=27 dirtied=3
  • I/O Timings: read=112.430