explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Qsz

Settings
# exclusive inclusive rows x rows loops node
1. 2.227 1,763.980 ↓ 353.5 707 1

Sort (cost=29,413.88..29,413.89 rows=2 width=4,496) (actual time=1,763.948..1,763.980 rows=707 loops=1)

  • Sort Key: cptydtldata.rpt_data_type, cptydtldata.display_order DESC
  • Sort Method: quicksort Memory: 1131kB
  • Buffers: shared hit=266126, temp read=9989 written=10011
2.          

CTE ftp

3. 0.011 0.530 ↑ 12.2 6 1

Sort (cost=98.58..98.77 rows=73 width=21) (actual time=0.530..0.530 rows=6 loops=1)

  • Sort Key: tp.display_order, tp.start_date
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=31
4. 0.012 0.519 ↑ 12.2 6 1

Nested Loop (cost=21.50..96.32 rows=73 width=21) (actual time=0.438..0.519 rows=6 loops=1)

  • Join Filter: ("overlaps"(mtp.start_date, mtp.end_date, tp.start_date, tp.end_date) AND (mtp.display_order >= tp.display_order))
  • Rows Removed by Join Filter: 102
  • Buffers: shared hit=31
5. 0.111 0.111 ↑ 1.0 1 1

Seq Scan on timeperiod mtp (cost=0.00..32.42 rows=1 width=20) (actual time=0.089..0.111 rows=1 loops=1)

  • Filter: ((time_period_cd = 'M201905'::text) AND (substr(time_period_cd, 1, 1) <> 'D'::text))
  • Rows Removed by Filter: 1083
  • Buffers: shared hit=13
6. 0.035 0.396 ↑ 6.1 108 1

Hash Join (cost=21.50..54.06 rows=656 width=29) (actual time=0.209..0.396 rows=108 loops=1)

  • Hash Cond: (tp.time_period_cd = atpc.time_period_cd)
  • Buffers: shared hit=18
7. 0.162 0.162 ↑ 6.4 172 1

Seq Scan on timeperiod tp (cost=0.00..29.65 rows=1,104 width=29) (actual time=0.003..0.162 rows=172 loops=1)

  • Filter: (substr(time_period_cd, 1, 1) <> 'D'::text)
  • Rows Removed by Filter: 912
  • Buffers: shared hit=13
8. 0.105 0.199 ↓ 1.0 668 1

Hash (cost=13.25..13.25 rows=660 width=9) (actual time=0.198..0.199 rows=668 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 36kB
  • Buffers: shared hit=5
9. 0.094 0.094 ↓ 1.0 668 1

Seq Scan on all_timeperiods_completed atpc (cost=0.00..13.25 rows=660 width=9) (actual time=0.006..0.094 rows=668 loops=1)

  • Filter: (source_system_cd = 'OU'::bpchar)
  • Buffers: shared hit=5
10.          

CTE ptydtldata

11. 12.127 14.803 ↓ 323.0 323 1

GroupAggregate (cost=978.76..981.95 rows=1 width=3,951) (actual time=2.734..14.803 rows=323 loops=1)

  • Group Key: ftp.display_order, bs.time_period_cd, bs.cpty_pno_id, bs.cpty_systemcd, bs.cpty_org_name
  • Buffers: shared hit=145
12. 0.853 2.676 ↓ 331.0 331 1

Sort (cost=978.76..978.76 rows=1 width=1,237) (actual time=2.647..2.676 rows=331 loops=1)

  • Sort Key: ftp.display_order, bs.time_period_cd, bs.cpty_pno_id, bs.cpty_systemcd, bs.cpty_org_name
  • Sort Method: quicksort Memory: 692kB
  • Buffers: shared hit=145
13. 0.286 1.823 ↓ 331.0 331 1

Nested Loop (cost=0.56..978.75 rows=1 width=1,237) (actual time=0.573..1.823 rows=331 loops=1)

  • Buffers: shared hit=145
14. 0.535 0.535 ↑ 12.2 6 1

CTE Scan on ftp (cost=0.00..1.46 rows=73 width=36) (actual time=0.531..0.535 rows=6 loops=1)

  • Buffers: shared hit=31
15. 1.002 1.002 ↓ 55.0 55 6

Index Scan using idx_bmk_summary on bmk_summary bs (cost=0.56..13.38 rows=1 width=1,233) (actual time=0.024..0.167 rows=55 loops=6)

  • Index Cond: ((source_system_cd = 'OU'::bpchar) AND (time_period_cd = ftp.time_period_cd) AND (assetclasscd = 'E'::bpchar) AND (partytype = 'I'::bpchar) AND ((pty_pno_id)::text = 'CL21'::text))
  • Filter: ((btrim((cpty_timezone_local_name)::text) = 'EST1'::text) AND (btrim((market_cd)::text) = 'US'::text) AND (btrim((security_location)::text) = 'US'::text) AND (btrim((cpty_currency_cd)::text) = 'USD'::text))
  • Buffers: shared hit=114
16.          

CTE cptydtldata

17. 233.782 1,758.014 ↓ 384.0 384 1

GroupAggregate (cost=28,329.89..28,333.08 rows=1 width=3,951) (actual time=1,485.118..1,758.014 rows=384 loops=1)

  • Group Key: ftp_1.display_order, bs_1.time_period_cd, bs_1.pty_pno_id, bs_1.pty_systemcd, bs_1.pty_org_name
  • Buffers: shared hit=266126, temp read=9989 written=10011
18. 243.762 1,524.232 ↓ 45,114.0 45,114 1

Sort (cost=28,329.89..28,329.90 rows=1 width=1,229) (actual time=1,484.773..1,524.232 rows=45,114 loops=1)

  • Sort Key: ftp_1.display_order, bs_1.time_period_cd, bs_1.pty_pno_id, bs_1.pty_systemcd, bs_1.pty_org_name
  • Sort Method: external merge Disk: 56056kB
  • Buffers: shared hit=266126, temp read=9989 written=10011
19. 24.038 1,280.470 ↓ 45,114.0 45,114 1

Nested Loop (cost=0.58..28,329.88 rows=1 width=1,229) (actual time=16.915..1,280.470 rows=45,114 loops=1)

  • Buffers: shared hit=266125
20. 0.192 16.226 ↓ 66.0 66 1

HashAggregate (cost=0.02..0.03 rows=1 width=516) (actual time=16.114..16.226 rows=66 loops=1)

  • Group Key: (ptydtldata_1.cpty_pno_id)::text
  • Buffers: shared hit=145
21. 16.034 16.034 ↓ 323.0 323 1

CTE Scan on ptydtldata ptydtldata_1 (cost=0.00..0.02 rows=1 width=516) (actual time=2.743..16.034 rows=323 loops=1)

  • Buffers: shared hit=145
22. 38.082 1,240.206 ↓ 684.0 684 66

Nested Loop (cost=0.56..28,329.84 rows=1 width=1,229) (actual time=1.018..18.791 rows=684 loops=66)

  • Buffers: shared hit=265980
23. 0.264 0.264 ↑ 12.2 6 66

CTE Scan on ftp ftp_1 (cost=0.00..1.46 rows=73 width=36) (actual time=0.000..0.004 rows=6 loops=66)

24. 1,201.860 1,201.860 ↓ 114.0 114 396

Index Scan using idx_bmk_summary on bmk_summary bs_1 (cost=0.56..388.05 rows=1 width=1,225) (actual time=0.833..3.035 rows=114 loops=396)

  • Index Cond: ((source_system_cd = 'OU'::bpchar) AND (time_period_cd = ftp_1.time_period_cd) AND (assetclasscd = 'E'::bpchar) AND ((pty_pno_id)::text = (ptydtldata_1.cpty_pno_id)::text))
  • Filter: ((partytype <> 'I'::bpchar) AND ((cpty_pno_id)::text <> 'CL21'::text) AND (btrim((cpty_timezone_local_name)::text) = 'EST1'::text) AND (btrim((market_cd)::text) = 'US'::text) AND (btrim((security_location)::text) = 'US'::text) AND (btrim((cpty_currency_cd)::text) = 'USD'::text))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=265980
25. 0.092 1,761.753 ↓ 353.5 707 1

Append (cost=0.00..0.07 rows=2 width=4,496) (actual time=1,485.136..1,761.753 rows=707 loops=1)

  • Buffers: shared hit=266126, temp read=9989 written=10011
26. 1,761.144 1,761.144 ↓ 384.0 384 1

CTE Scan on cptydtldata (cost=0.00..0.04 rows=1 width=4,496) (actual time=1,485.135..1,761.144 rows=384 loops=1)

  • Buffers: shared hit=266126, temp read=9989 written=10011
27. 0.517 0.517 ↓ 323.0 323 1

CTE Scan on ptydtldata (cost=0.00..0.04 rows=1 width=4,496) (actual time=0.006..0.517 rows=323 loops=1)