explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CKbU

Settings
# exclusive inclusive rows x rows loops node
1. 3,153.542 3,153.542 ↓ 9.1 6,542 1

CTE Scan on factset_fund_holdings h (cost=215,668.16..218,885.45 rows=715 width=100) (actual time=2,855.770..3,153.542 rows=6,542 loops=1)

  • Filter: (pos = 1)
  • Rows Removed by Filter: 74181
2.          

CTE report_dates

3. 292.973 292.973 ↓ 1.0 556,339 1

Seq Scan on q4_fund_dates (cost=0.00..33,672.26 rows=535,787 width=13) (actual time=0.014..292.973 rows=556,339 loops=1)

  • Filter: ((report_date >= '2017-01-21'::date) AND (report_date <= '2019-01-21'::date))
  • Rows Removed by Filter: 1071773
4.          

CTE factset_fund_holdings

5. 79.147 3,110.801 ↑ 1.8 80,723 1

WindowAgg (cost=177,081.22..181,995.89 rows=142,991 width=46) (actual time=2,855.765..3,110.801 rows=80,723 loops=1)

6. 98.460 3,031.654 ↑ 1.8 80,723 1

Merge Left Join (cost=177,081.22..179,493.55 rows=142,991 width=54) (actual time=2,855.757..3,031.654 rows=80,723 loops=1)

  • Merge Cond: ((own_fund_detail.factset_fund_id = adjh.factset_fund_id) AND (report_dates.report_date = adjh.report_date))
  • Join Filter: (adjh.fsym_id = sar.fsym_id)
7. 137.731 2,037.337 ↑ 1.8 80,723 1

Sort (cost=104,544.74..104,902.22 rows=142,991 width=22) (actual time=2,012.292..2,037.337 rows=80,723 loops=1)

  • Sort Key: own_fund_detail.factset_fund_id, report_dates.report_date DESC
  • Sort Method: quicksort Memory: 9379kB
8. 203.300 1,899.606 ↑ 1.8 80,723 1

Merge Join (cost=90,142.46..92,300.74 rows=142,991 width=22) (actual time=1,497.249..1,899.606 rows=80,723 loops=1)

  • Merge Cond: (report_dates.factset_fund_id = own_fund_detail.factset_fund_id)
9. 577.189 1,456.232 ↓ 207.7 556,339 1

Sort (cost=13,582.44..13,589.14 rows=2,679 width=49) (actual time=1,275.988..1,456.232 rows=556,339 loops=1)

  • Sort Key: report_dates.factset_fund_id
  • Sort Method: external merge Disk: 19040kB
10. 229.956 879.043 ↓ 207.7 556,339 1

Nested Loop (cost=0.42..13,429.90 rows=2,679 width=49) (actual time=0.039..879.043 rows=556,339 loops=1)

11. 0.023 0.023 ↑ 1.0 1 1

Index Only Scan using own_sec_coverage_fsym_id_idx on own_sec_coverage sar (cost=0.42..8.44 rows=1 width=9) (actual time=0.021..0.023 rows=1 loops=1)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Fetches: 1
12. 649.064 649.064 ↓ 207.7 556,339 1

CTE Scan on report_dates (cost=0.00..13,394.68 rows=2,679 width=40) (actual time=0.017..649.064 rows=556,339 loops=1)

  • Filter: ((report_date >= '2017-01-21'::date) AND (report_date <= '2019-01-21'::date))
13. 41.940 240.074 ↓ 7.6 80,913 1

Sort (cost=76,560.02..76,586.71 rows=10,675 width=27) (actual time=221.254..240.074 rows=80,913 loops=1)

  • Sort Key: own_fund_detail.factset_fund_id
  • Sort Method: quicksort Memory: 718kB
14. 5.533 198.134 ↑ 1.6 6,732 1

Hash Join (cost=75,293.91..75,845.76 rows=10,675 width=27) (actual time=190.321..198.134 rows=6,732 loops=1)

  • Hash Cond: (own_fund_detail.factset_fund_id = ent.factset_fund_id)
15. 62.376 130.960 ↑ 2.3 7,977 1

HashAggregate (cost=70,597.26..70,784.67 rows=18,741 width=18) (actual time=128.538..130.960 rows=7,977 loops=1)

  • Group Key: own_fund_detail.fsym_id, own_fund_detail.factset_fund_id
16. 56.748 68.584 ↓ 7.7 143,428 1

Bitmap Heap Scan on own_fund_detail (cost=437.87..70,503.52 rows=18,748 width=18) (actual time=14.077..68.584 rows=143,428 loops=1)

  • Recheck Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Blocks: exact=14383
17. 11.836 11.836 ↓ 7.8 145,949 1

Bitmap Index Scan on own_fund_detail_fsym_id_idx (cost=0.00..433.18 rows=18,748 width=0) (actual time=11.836..11.836 rows=145,949 loops=1)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
18. 21.893 61.641 ↓ 1.0 68,127 1

Hash (cost=3,847.24..3,847.24 rows=67,953 width=9) (actual time=61.641..61.641 rows=68,127 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 3752kB
19. 39.748 39.748 ↓ 1.0 68,127 1

Seq Scan on own_ent_funds ent (cost=0.00..3,847.24 rows=67,953 width=9) (actual time=0.020..39.748 rows=68,127 loops=1)

  • Filter: (active_flag = 1)
  • Rows Removed by Filter: 51172
20. 605.742 895.857 ↓ 7.7 143,428 1

Sort (cost=72,536.47..72,583.33 rows=18,741 width=54) (actual time=843.444..895.857 rows=143,428 loops=1)

  • Sort Key: adjh.factset_fund_id, adjh.report_date DESC
  • Sort Method: quicksort Memory: 26310kB
21. 58.043 290.115 ↓ 7.7 143,428 1

Subquery Scan on adjh (cost=70,831.61..71,206.43 rows=18,741 width=54) (actual time=167.430..290.115 rows=143,428 loops=1)

22. 163.361 232.072 ↓ 7.7 143,428 1

HashAggregate (cost=70,831.61..71,019.02 rows=18,741 width=54) (actual time=167.428..232.072 rows=143,428 loops=1)

  • Group Key: own_fund_detail_1.factset_fund_id, own_fund_detail_1.fsym_id, own_fund_detail_1.adj_holding, own_fund_detail_1.report_date, own_fund_detail_1.reported_holding, own_fund_detail_1.adj_mv, own_fund_detail_1.reported_mv
23. 56.877 68.711 ↓ 7.7 143,428 1

Bitmap Heap Scan on own_fund_detail own_fund_detail_1 (cost=437.87..70,503.52 rows=18,748 width=54) (actual time=14.093..68.711 rows=143,428 loops=1)

  • Recheck Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Blocks: exact=14383
24. 11.834 11.834 ↓ 7.8 145,949 1

Bitmap Index Scan on own_fund_detail_fsym_id_idx (cost=0.00..433.18 rows=18,748 width=0) (actual time=11.834..11.834 rows=145,949 loops=1)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)