explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Yq7K

Settings
# exclusive inclusive rows x rows loops node
1. 151.342 336,739.109 ↓ 80,367.0 642,936 1

Subquery Scan on x (cost=2,361,781.52..2,361,816.37 rows=8 width=568) (actual time=335,988.567..336,739.109 rows=642,936 loops=1)

  • Filter: (x.rownum = 1)
  • Rows Removed by Filter: 1,332,146
2. 1,851.237 336,587.767 ↓ 1,275.1 1,975,082 1

HashAggregate (cost=2,361,781.52..2,361,797.01 rows=1,549 width=576) (actual time=335,988.564..336,587.767 rows=1,975,082 loops=1)

  • Group Key: "*SELECT* 1".mnemonic, "*SELECT* 1".companyid, "*SELECT* 1".periodenddate, "*SELECT* 1".filingdate, "*SELECT* 1".dataitemvalue, "*SELECT* 1".rownum
3. 144.465 334,736.530 ↓ 1,275.1 1,975,082 1

Append (cost=1,293,229.38..2,361,758.29 rows=1,549 width=576) (actual time=275,455.810..334,736.530 rows=1,975,082 loops=1)

4. 165.298 277,942.610 ↓ 1,386.5 1,684,588 1

Subquery Scan on *SELECT* 1 (cost=1,293,229.38..1,293,277.98 rows=1,215 width=77) (actual time=275,455.810..277,942.610 rows=1,684,588 loops=1)

5. 1,342.885 277,777.312 ↓ 1,386.5 1,684,588 1

WindowAgg (cost=1,293,229.38..1,293,265.83 rows=1,215 width=87) (actual time=275,455.809..277,777.312 rows=1,684,588 loops=1)

6. 6,532.236 276,434.427 ↓ 1,386.5 1,684,588 1

Sort (cost=1,293,229.38..1,293,232.41 rows=1,215 width=60) (actual time=275,455.787..276,434.427 rows=1,684,588 loops=1)

  • Sort Key: hm.mnemonic, fi.financialperiodid, fi.filingdate DESC, fd.financialcollectionid DESC
  • Sort Method: external merge Disk: 121,176kB
7. 0.000 269,902.191 ↓ 1,386.5 1,684,588 1

Gather (cost=11,323.87..1,293,167.13 rows=1,215 width=60) (actual time=2,263.820..269,902.191 rows=1,684,588 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 481.080 272,605.110 ↓ 1,109.7 561,529 3 / 3

Nested Loop (cost=10,323.87..1,292,045.63 rows=506 width=60) (actual time=2,255.769..272,605.110 rows=561,529 loops=3)

9. 155.251 270,439.442 ↓ 1,369.6 561,529 3 / 3

Nested Loop (cost=10,323.44..1,288,819.16 rows=410 width=56) (actual time=2,255.741..270,439.442 rows=561,529 loops=3)

10. 143.734 76,497.358 ↓ 563.8 64,833 3 / 3

Nested Loop (cost=10,322.73..953,796.85 rows=115 width=36) (actual time=2,254.777..76,497.358 rows=64,833 loops=3)

11. 41.206 33,449.894 ↓ 541.7 83,960 3 / 3

Nested Loop (cost=10,322.15..952,554.63 rows=155 width=30) (actual time=2,254.752..33,449.894 rows=83,960 loops=3)

12. 1,327.704 5,871.539 ↓ 478.2 3,826 3 / 3

Hash Join (cost=10,321.58..950,032.75 rows=8 width=30) (actual time=2,253.874..5,871.539 rows=3,826 loops=3)

  • Hash Cond: (fi.financialperiodid = fp.financialperiodid)
13. 2,299.947 2,299.947 ↑ 1.3 11,208,544 3 / 3

Parallel Seq Scan on ciqfininstance fi (cost=0.00..886,427.20 rows=14,209,038 width=24) (actual time=0.007..2,299.947 rows=11,208,544 loops=3)

  • Filter: ((filingdate <= '2019-12-31 00:00:00'::timestamp without time zone) AND (periodenddate >= '1999-12-31 00:00:00'::timestamp without time zone) AND (periodenddate <= '2019-12-31 00:00:00'::timestamp without time zone) AND (latestfilingforinstanceflag = 1))
  • Rows Removed by Filter: 2,976,017
14. 1.617 2,243.888 ↓ 443.9 4,439 3 / 3

Hash (cost=10,321.45..10,321.45 rows=10 width=10) (actual time=2,243.888..2,243.888 rows=4,439 loops=3)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 255kB
15. 1.212 2,242.271 ↓ 443.9 4,439 3 / 3

Nested Loop (cost=1.16..10,321.45 rows=10 width=10) (actual time=1.132..2,242.271 rows=4,439 loops=3)

16. 0.223 15.859 ↑ 1.0 50 3 / 3

Nested Loop (cost=0.72..460.11 rows=51 width=8) (actual time=0.348..15.859 rows=50 loops=3)

17. 0.086 0.086 ↑ 1.0 50 3 / 3

Index Only Scan using uix_hpscompanyid_companyid on hpscompanyid hc (cost=0.28..28.91 rows=51 width=4) (actual time=0.028..0.086 rows=50 loops=3)

  • Index Cond: ((companyid >= 28,312) AND (companyid <= 30,345))
  • Heap Fetches: 150
18. 15.550 15.550 ↑ 1.0 1 150 / 3

Index Only Scan using pk_ciqcompany on ciqcompany c (cost=0.44..8.46 rows=1 width=4) (actual time=0.311..0.311 rows=1 loops=150)

  • Index Cond: (companyid = hc.companyid)
  • Heap Fetches: 150
19. 2,225.200 2,225.200 ↓ 1.7 89 150 / 3

Index Scan using ix_ciqfinperiod_companyid_periodtypeid on ciqfinperiod fp (cost=0.44..192.85 rows=51 width=10) (actual time=7.108..44.504 rows=89 loops=150)

  • Index Cond: ((companyid = c.companyid) AND (periodtypeid = 4))
20. 27,537.149 27,537.149 ↑ 3.9 22 11,477 / 3

Index Only Scan using pk_ciqfininstancetocollection on ciqfininstancetocollection ic (cost=0.57..314.39 rows=85 width=8) (actual time=1.372..7.198 rows=22 loops=11,477)

  • Index Cond: (financialinstanceid = fi.financialinstanceid)
  • Heap Fetches: 251,881
21. 42,903.730 42,903.730 ↑ 1.0 1 251,881 / 3

Index Scan using pk_ciqfincollection on ciqfincollection fc (cost=0.57..8.01 rows=1 width=6) (actual time=0.511..0.511 rows=1 loops=251,881)

  • Index Cond: (financialcollectionid = ic.financialcollectionid)
  • Filter: (currencyid <> 0)
  • Rows Removed by Filter: 0
22. 17,699.500 193,786.833 ↑ 36.9 9 194,500 / 3

Nested Loop (cost=0.71..2,909.92 rows=332 width=28) (actual time=0.742..2.989 rows=9 loops=194,500)

23. 3,890.000 3,890.000 ↑ 1.0 332 194,500 / 3

Seq Scan on hpsmnemonic hm (cost=0.00..8.24 rows=332 width=21) (actual time=0.003..0.060 rows=332 loops=194,500)

  • Filter: ((source)::text = 'ciqFinCollectionData'::text)
  • Rows Removed by Filter: 7
24. 172,197.333 172,197.333 ↓ 0.0 0 64,574,000 / 3

Index Scan using pk_ciqfincollectiondata on ciqfincollectiondata fd (cost=0.71..8.73 rows=1 width=15) (actual time=0.008..0.008 rows=0 loops=64,574,000)

  • Index Cond: ((financialcollectionid = fc.financialcollectionid) AND (dataitemid = hm.dataitemid))
25. 1,684.588 1,684.588 ↑ 1.0 1 1,684,588 / 3

Index Scan using pk_ciqexchangerate on ciqexchangerate xr (cost=0.43..7.86 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=1,684,588)

  • Index Cond: ((currencyid = fc.currencyid) AND (pricedate = fi.periodenddate))
  • Filter: (latestsnapflag = 1)
26. 32.467 56,649.455 ↓ 869.7 290,494 1

Subquery Scan on *SELECT* 2 (cost=1,068,460.04..1,068,472.56 rows=334 width=52) (actual time=56,244.958..56,649.455 rows=290,494 loops=1)

27. 174.915 56,616.988 ↓ 869.7 290,494 1

WindowAgg (cost=1,068,460.04..1,068,469.22 rows=334 width=62) (actual time=56,244.956..56,616.988 rows=290,494 loops=1)

28. 879.144 56,442.073 ↓ 869.7 290,494 1

Sort (cost=1,068,460.04..1,068,460.87 rows=334 width=54) (actual time=56,244.941..56,442.073 rows=290,494 loops=1)

  • Sort Key: hm_1.mnemonic, fi_1.financialperiodid, fi_1.filingdate DESC, fd_1.financialcollectionid DESC
  • Sort Method: external merge Disk: 20,872kB
29. 0.000 55,562.929 ↓ 869.7 290,494 1

Gather (cost=11,323.09..1,068,446.04 rows=334 width=54) (actual time=34.094..55,562.929 rows=290,494 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
30. 41.136 55,871.924 ↓ 696.6 96,831 3 / 3

Nested Loop (cost=10,323.09..1,067,412.64 rows=139 width=54) (actual time=21.850..55,871.924 rows=96,831 loops=3)

31. 93.766 4,187.888 ↓ 490.4 19,127 3 / 3

Nested Loop (cost=10,322.38..953,796.38 rows=39 width=34) (actual time=21.458..4,187.888 rows=19,127 loops=3)

32. 25.986 3,758.281 ↓ 541.7 83,960 3 / 3

Nested Loop (cost=10,321.80..952,554.20 rows=155 width=30) (actual time=21.363..3,758.281 rows=83,960 loops=3)

33. 1,270.093 3,602.222 ↓ 478.2 3,826 3 / 3

Hash Join (cost=10,321.23..950,032.40 rows=8 width=30) (actual time=21.317..3,602.222 rows=3,826 loops=3)

  • Hash Cond: (fi_1.financialperiodid = fp_1.financialperiodid)
34. 2,325.113 2,325.113 ↑ 1.3 11,208,544 3 / 3

Parallel Seq Scan on ciqfininstance fi_1 (cost=0.00..886,427.20 rows=14,209,038 width=24) (actual time=0.011..2,325.113 rows=11,208,544 loops=3)

  • Filter: ((filingdate <= '2019-12-31 00:00:00'::timestamp without time zone) AND (periodenddate >= '1999-12-31 00:00:00'::timestamp without time zone) AND (periodenddate <= '2019-12-31 00:00:00'::timestamp without time zone) AND (latestfilingforinstanceflag = 1))
  • Rows Removed by Filter: 2,976,017
35. 0.992 7.016 ↓ 443.9 4,439 3 / 3

Hash (cost=10,321.10..10,321.10 rows=10 width=10) (actual time=7.016..7.016 rows=4,439 loops=3)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 255kB
36. 0.786 6.024 ↓ 443.9 4,439 3 / 3

Nested Loop (cost=1.16..10,321.10 rows=10 width=10) (actual time=0.095..6.024 rows=4,439 loops=3)

37. 0.038 0.288 ↑ 1.0 50 3 / 3

Nested Loop (cost=0.72..460.11 rows=51 width=8) (actual time=0.057..0.288 rows=50 loops=3)

38. 0.050 0.050 ↑ 1.0 50 3 / 3

Index Only Scan using uix_hpscompanyid_companyid on hpscompanyid hc_1 (cost=0.28..28.91 rows=51 width=4) (actual time=0.033..0.050 rows=50 loops=3)

  • Index Cond: ((companyid >= 28,312) AND (companyid <= 30,345))
  • Heap Fetches: 150
39. 0.200 0.200 ↑ 1.0 1 150 / 3

Index Only Scan using pk_ciqcompany on ciqcompany c_1 (cost=0.44..8.46 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=150)

  • Index Cond: (companyid = hc_1.companyid)
  • Heap Fetches: 150
40. 4.950 4.950 ↓ 1.7 89 150 / 3

Index Scan using ix_ciqfinperiod_companyid_periodtypeid on ciqfinperiod fp_1 (cost=0.44..192.84 rows=51 width=10) (actual time=0.013..0.099 rows=89 loops=150)

  • Index Cond: ((companyid = c_1.companyid) AND (periodtypeid = 4))
41. 130.073 130.073 ↑ 3.9 22 11,477 / 3

Index Only Scan using pk_ciqfininstancetocollection on ciqfininstancetocollection ic_1 (cost=0.57..314.38 rows=85 width=8) (actual time=0.013..0.034 rows=22 loops=11,477)

  • Index Cond: (financialinstanceid = fi_1.financialinstanceid)
  • Heap Fetches: 251,881
42. 335.841 335.841 ↓ 0.0 0 251,881 / 3

Index Scan using pk_ciqfincollection on ciqfincollection fc_1 (cost=0.57..8.01 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=251,881)

  • Index Cond: (financialcollectionid = ic_1.financialcollectionid)
  • Filter: (currencyid = 0)
  • Rows Removed by Filter: 1
43. 6,025.005 51,642.900 ↑ 66.4 5 57,381 / 3

Nested Loop (cost=0.71..2,909.92 rows=332 width=28) (actual time=0.801..2.700 rows=5 loops=57,381)

44. 1,166.747 1,166.747 ↑ 1.0 332 57,381 / 3

Seq Scan on hpsmnemonic hm_1 (cost=0.00..8.24 rows=332 width=21) (actual time=0.002..0.061 rows=332 loops=57,381)

  • Filter: ((source)::text = 'ciqFinCollectionData'::text)
  • Rows Removed by Filter: 7
45. 44,451.148 44,451.148 ↓ 0.0 0 19,050,492 / 3

Index Scan using pk_ciqfincollectiondata on ciqfincollectiondata fd_1 (cost=0.71..8.73 rows=1 width=15) (actual time=0.007..0.007 rows=0 loops=19,050,492)

  • Index Cond: ((financialcollectionid = fc_1.financialcollectionid) AND (dataitemid = hm_1.dataitemid))
Planning time : 10.925 ms
Execution time : 336,795.636 ms