explain.depesz.com

PostgreSQL's explain analyze made readable

Result: USy9 : restructure

Settings
# exclusive inclusive rows x rows loops node
1. 0.040 9.601 ↑ 7.1 28 1

Sort (cost=83,636.43..83,636.93 rows=200 width=128) (actual time=9.599..9.601 rows=28 loops=1)

  • Sort Key: (sum(tmp2.pctos)) DESC, (COALESCE(tmp2.investor, 'Other Investor Types'::character varying)), (sum((sum(query_block.pctos)))) DESC, query_block.mat_year
  • Sort Method: quicksort Memory: 28kB
2.          

CTE secmas_cusips

3. 0.629 0.629 ↓ 43.0 43 1

Index Scan using pk_own_secmas_1442104178 on own_secmas (cost=0.56..407.74 rows=1 width=10) (actual time=0.159..0.629 rows=43 loops=1)

  • Index Cond: ((cusip)::text = ANY ('{023135BM7,023135BJ4,023135BF2,023135BC9,023135AZ9,023135AW6,023135AT3,023135BN5,U02320AK2,U02320AJ5,U02320AH9,U02320AG1,U02320AF3,U02320AE6,U02320AD8,023135BL9,023135BK1,023135BH8,023135BG0,023135BE5,023135BD7,023135BB1,023135BA3,023135AY2,023135AX4,023135AV8,023135AU0,023135AS5,023135AR7,966837AE6,U96710AA3,966837AD8,023135AQ9,023135AP1,023135AN6,023135AM8,023135AL0,023135AJ5,023135AH9,023135AG1,FDS0U6IA9,FDS0O2PP4,FDS0LXH52,FDS0F9JT8,FDS095W76,FDSD87D01,FDS8E8841,FDS8E84C7,FDS46A927,FDS0FUQ89,FDS0FUQ97}'::text[]))
  • Filter: (lower((fi_flag)::text) = 'true'::text)
  • Rows Removed by Filter: 1
4.          

CTE query_block

5. 1.306 8.352 ↓ 4.1 842 1

Nested Loop (cost=28,401.56..83,181.23 rows=206 width=76) (actual time=3.504..8.352 rows=842 loops=1)

6. 0.550 3.678 ↑ 8.3 842 1

HashAggregate (cost=28,401.13..28,471.04 rows=6,991 width=88) (actual time=3.483..3.678 rows=842 loops=1)

  • Group Key: hh.company_number, hh.cusip, hh."position
7. 0.067 3.128 ↑ 8.3 842 1

Append (cost=0.59..28,348.70 rows=6,991 width=88) (actual time=0.706..3.128 rows=842 loops=1)

8. 0.024 1.084 ↓ 4.5 9 1

Nested Loop (cost=0.59..12.66 rows=2 width=22) (actual time=0.706..1.084 rows=9 loops=1)

9. 0.027 0.673 ↓ 43.0 43 1

HashAggregate (cost=0.02..0.03 rows=1 width=36) (actual time=0.666..0.673 rows=43 loops=1)

  • Group Key: (secmas_cusips.cusip)::bpchar
10. 0.646 0.646 ↓ 43.0 43 1

CTE Scan on secmas_cusips (cost=0.00..0.02 rows=1 width=36) (actual time=0.161..0.646 rows=43 loops=1)

11. 0.387 0.387 ↓ 0.0 0 43

Index Scan using "IX_own_schedule_d_hist_snapshot_ix_own_schedule_d_hist_snapshot" on own_schedule_d_hist_snapshot hh (cost=0.56..12.60 rows=2 width=22) (actual time=0.009..0.009 rows=0 loops=43)

  • Index Cond: ((cusip = (secmas_cusips.cusip)::bpchar) AND (report_date = '2019-03-31'::date))
12. 1.977 1.977 ↑ 8.4 833 1

Index Scan using "IX_own_snapshot_ix_own_snapshot_cusip" on own_snapshot hh_1 (cost=0.57..28,266.13 rows=6,989 width=48) (actual time=0.155..1.977 rows=833 loops=1)

  • Index Cond: ((cusip = ANY ('{023135BM7,023135BJ4,023135BF2,023135BC9,023135AZ9,023135AW6,023135AT3,023135BN5,U02320AK2,U02320AJ5,U02320AH9,U02320AG1,U02320AF3,U02320AE6,U02320AD8,023135BL9,023135BK1,023135BH8,023135BG0,023135BE5,023135BD7,023135BB1,023135BA3,023135AY2,023135AX4,023135AV8,023135AU0,023135AS5,023135AR7,966837AE6,U96710AA3,966837AD8,023135AQ9,023135AP1,023135AN6,023135AM8,023135AL0,023135AJ5,023135AH9,023135AG1,FDS0U6IA9,FDS0O2PP4,FDS0LXH52,FDS0F9JT8,FDS095W76,FDSD87D01,FDS8E8841,FDS8E84C7,FDS46A927,FDS0FUQ89,FDS0FUQ97}'::bpchar[])) AND (report_date = '2019-03-31'::date))
13. 3.368 3.368 ↑ 1.0 1 842

Index Scan using pk_own_portmas_ultimate_1648776981 on own_portmas_ultimate pu (cost=0.43..7.81 rows=1 width=18) (actual time=0.004..0.004 rows=1 loops=842)

  • Index Cond: ((company_number = hh.company_number) AND (portfolio_type = 'F'::bpchar))
14. 0.043 9.561 ↑ 7.1 28 1

HashAggregate (cost=36.81..39.81 rows=200 width=128) (actual time=9.546..9.561 rows=28 loops=1)

  • Group Key: COALESCE(tmp2.investor, 'Other Investor Types'::character varying), query_block.mat_year
15. 0.019 9.518 ↑ 5.9 34 1

Hash Left Join (cost=27.80..34.81 rows=200 width=128) (actual time=9.495..9.518 rows=34 loops=1)

  • Hash Cond: ((query_block.investor)::text = (tmp2.investor)::text)
16. 0.400 9.136 ↑ 5.9 34 1

HashAggregate (cost=5.67..8.16 rows=200 width=142) (actual time=9.122..9.136 rows=34 loops=1)

  • Group Key: query_block.investor, query_block.mat_year
17. 8.736 8.736 ↓ 4.1 842 1

CTE Scan on query_block (cost=0.00..4.12 rows=206 width=142) (actual time=3.509..8.736 rows=842 loops=1)

18. 0.005 0.363 ↑ 16.8 4 1

Hash (cost=21.29..21.29 rows=67 width=110) (actual time=0.363..0.363 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.002 0.358 ↑ 16.8 4 1

Subquery Scan on tmp2 (cost=15.29..21.29 rows=67 width=110) (actual time=0.348..0.358 rows=4 loops=1)

  • Filter: (tmp2.rnk <= 4)
  • Rows Removed by Filter: 3
20. 0.012 0.356 ↑ 28.6 7 1

WindowAgg (cost=15.29..18.79 rows=200 width=118) (actual time=0.347..0.356 rows=7 loops=1)

21. 0.015 0.344 ↑ 28.6 7 1

Sort (cost=15.29..15.79 rows=200 width=110) (actual time=0.344..0.344 rows=7 loops=1)

  • Sort Key: (sum(query_block_1.pctos)) DESC
  • Sort Method: quicksort Memory: 25kB
22. 0.257 0.329 ↑ 28.6 7 1

HashAggregate (cost=5.15..7.65 rows=200 width=110) (actual time=0.326..0.329 rows=7 loops=1)

  • Group Key: query_block_1.investor
23. 0.072 0.072 ↓ 4.1 842 1

CTE Scan on query_block query_block_1 (cost=0.00..4.12 rows=206 width=110) (actual time=0.000..0.072 rows=842 loops=1)

Planning time : 1.538 ms
Execution time : 10.080 ms