explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FhlD

Settings
# exclusive inclusive rows x rows loops node
1. 142.831 110,459.622 ↓ 82,481.4 659,851 1

Subquery Scan on x (cost=2,361,377.01..2,361,411.86 rows=8 width=568) (actual time=109,750.329..110,459.622 rows=659,851 loops=1)

  • Filter: (x.rownum = 1)
  • Rows Removed by Filter: 1,381,633
2. 1,765.977 110,316.791 ↓ 1,317.9 2,041,484 1

HashAggregate (cost=2,361,377.01..2,361,392.50 rows=1,549 width=576) (actual time=109,750.325..110,316.791 rows=2,041,484 loops=1)

  • Group Key: "*SELECT* 1".mnemonic, "*SELECT* 1".companyid, "*SELECT* 1".periodenddate, "*SELECT* 1".filingdate, "*SELECT* 1".dataitemvalue, "*SELECT* 1".rownum
3. 138.756 108,550.814 ↓ 1,317.9 2,041,484 1

Append (cost=1,293,027.12..2,361,353.77 rows=1,549 width=576) (actual time=80,385.001..108,550.814 rows=2,041,484 loops=1)

4. 161.533 82,762.960 ↓ 1,447.4 1,758,586 1

Subquery Scan on *SELECT* 1 (cost=1,293,027.12..1,293,075.72 rows=1,215 width=77) (actual time=80,385.000..82,762.960 rows=1,758,586 loops=1)

5. 1,315.530 82,601.427 ↓ 1,447.4 1,758,586 1

WindowAgg (cost=1,293,027.12..1,293,063.57 rows=1,215 width=87) (actual time=80,384.999..82,601.427 rows=1,758,586 loops=1)

6. 6,240.079 81,285.897 ↓ 1,447.4 1,758,586 1

Sort (cost=1,293,027.12..1,293,030.15 rows=1,215 width=60) (actual time=80,384.973..81,285.897 rows=1,758,586 loops=1)

  • Sort Key: hm.mnemonic, fi.financialperiodid, fi.filingdate DESC, fd.financialcollectionid DESC
  • Sort Method: external merge Disk: 126,440kB
7. 0.000 75,045.818 ↓ 1,447.4 1,758,586 1

Gather (cost=11,121.61..1,292,964.87 rows=1,215 width=60) (actual time=28.133..75,045.818 rows=1,758,586 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 584.266 77,855.276 ↓ 1,158.5 586,195 3 / 3

Nested Loop (cost=10,121.61..1,291,843.37 rows=506 width=60) (actual time=19.921..77,855.276 rows=586,195 loops=3)

9. 80.939 76,098.619 ↓ 1,429.7 586,195 3 / 3

Nested Loop (cost=10,121.18..1,288,616.90 rows=410 width=56) (actual time=19.895..76,098.619 rows=586,195 loops=3)

10. 52.001 3,879.217 ↓ 603.7 69,431 3 / 3

Nested Loop (cost=10,120.47..953,594.59 rows=115 width=36) (actual time=18.604..3,879.217 rows=69,431 loops=3)

11. 22.768 3,468.789 ↓ 578.1 89,607 3 / 3

Nested Loop (cost=10,119.89..952,352.37 rows=155 width=30) (actual time=18.581..3,468.789 rows=89,607 loops=3)

12. 1,193.606 3,332.770 ↓ 505.6 4,045 3 / 3

Hash Join (cost=10,119.32..949,830.49 rows=8 width=30) (actual time=18.546..3,332.770 rows=4,045 loops=3)

  • Hash Cond: (fi.financialperiodid = fp.financialperiodid)
13. 2,132.435 2,132.435 ↑ 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.008..2,132.435 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.058 6.729 ↓ 461.7 4,617 3 / 3

Hash (cost=10,119.19..10,119.19 rows=10 width=10) (actual time=6.726..6.729 rows=4,617 loops=3)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 263kB
15. 0.908 5.671 ↓ 461.7 4,617 3 / 3

Nested Loop (cost=1.16..10,119.19 rows=10 width=10) (actual time=0.090..5.671 rows=4,617 loops=3)

16. 0.069 0.263 ↑ 1.0 50 3 / 3

Nested Loop (cost=0.72..451.21 rows=50 width=8) (actual time=0.049..0.263 rows=50 loops=3)

17. 0.044 0.044 ↑ 1.0 50 3 / 3

Index Only Scan using uix_hpscompanyid_companyid on hpscompanyid hc (cost=0.28..28.46 rows=50 width=4) (actual time=0.031..0.044 rows=50 loops=3)

  • Index Cond: ((companyid >= 1) AND (companyid <= 24,686))
  • Heap Fetches: 150
18. 0.150 0.150 ↑ 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.003..0.003 rows=1 loops=150)

  • Index Cond: (companyid = hc.companyid)
  • Heap Fetches: 150
19. 4.500 4.500 ↓ 1.8 92 150 / 3

Index Scan using ix_ciqfinperiod_companyid_periodtypeid on ciqfinperiod fp (cost=0.44..192.85 rows=51 width=10) (actual time=0.013..0.090 rows=92 loops=150)

  • Index Cond: ((companyid = c.companyid) AND (periodtypeid = 4))
20. 113.251 113.251 ↑ 3.9 22 12,134 / 3

Index Only Scan using pk_ciqfininstancetocollection on ciqfininstancetocollection ic (cost=0.57..314.39 rows=85 width=8) (actual time=0.011..0.028 rows=22 loops=12,134)

  • Index Cond: (financialinstanceid = fi.financialinstanceid)
  • Heap Fetches: 268,820
21. 358.427 358.427 ↑ 1.0 1 268,820 / 3

Index Scan using pk_ciqfincollection on ciqfincollection fc (cost=0.57..8.01 rows=1 width=6) (actual time=0.004..0.004 rows=1 loops=268,820)

  • Index Cond: (financialcollectionid = ic.financialcollectionid)
  • Filter: (currencyid <> 0)
  • Rows Removed by Filter: 0
22. 0.000 72,138.463 ↑ 41.5 8 208,292 / 3

Nested Loop (cost=0.71..2,909.92 rows=332 width=28) (actual time=0.677..1.039 rows=8 loops=208,292)

23. 3,818.687 3,818.687 ↑ 1.0 332 208,292 / 3

Seq Scan on hpsmnemonic hm (cost=0.00..8.24 rows=332 width=21) (actual time=0.002..0.055 rows=332 loops=208,292)

  • Filter: ((source)::text = 'ciqFinCollectionData'::text)
  • Rows Removed by Filter: 7
24. 69,152.944 69,152.944 ↓ 0.0 0 69,152,944 / 3

Index Scan using pk_ciqfincollectiondata on ciqfincollectiondata fd (cost=0.71..8.73 rows=1 width=15) (actual time=0.003..0.003 rows=0 loops=69,152,944)

  • Index Cond: ((financialcollectionid = fc.financialcollectionid) AND (dataitemid = hm.dataitemid))
25. 1,172.391 1,172.391 ↑ 1.0 1 1,758,586 / 3

Index Scan using pk_ciqexchangerate on ciqexchangerate xr (cost=0.43..7.86 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=1,758,586)

  • Index Cond: ((currencyid = fc.currencyid) AND (pricedate = fi.periodenddate))
  • Filter: (latestsnapflag = 1)
  • Rows Removed by Filter: 0
26. 28.150 25,649.098 ↓ 847.0 282,898 1

Subquery Scan on *SELECT* 2 (cost=1,068,257.79..1,068,270.31 rows=334 width=52) (actual time=25,308.711..25,649.098 rows=282,898 loops=1)

27. 147.115 25,620.948 ↓ 847.0 282,898 1

WindowAgg (cost=1,068,257.79..1,068,266.97 rows=334 width=62) (actual time=25,308.710..25,620.948 rows=282,898 loops=1)

28. 742.686 25,473.833 ↓ 847.0 282,898 1

Sort (cost=1,068,257.79..1,068,258.62 rows=334 width=54) (actual time=25,308.696..25,473.833 rows=282,898 loops=1)

  • Sort Key: hm_1.mnemonic, fi_1.financialperiodid, fi_1.filingdate DESC, fd_1.financialcollectionid DESC
  • Sort Method: external merge Disk: 20,240kB
29. 0.000 24,731.147 ↓ 847.0 282,898 1

Gather (cost=11,120.83..1,068,243.79 rows=334 width=54) (actual time=26.961..24,731.147 rows=282,898 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
30. 20.392 25,048.735 ↓ 678.4 94,299 3 / 3

Nested Loop (cost=10,120.83..1,067,210.39 rows=139 width=54) (actual time=18.912..25,048.735 rows=94,299 loops=3)

31. 12.671 3,621.607 ↓ 517.3 20,176 3 / 3

Nested Loop (cost=10,120.12..953,594.12 rows=39 width=34) (actual time=18.406..3,621.607 rows=20,176 loops=3)

32. 16.036 3,250.509 ↓ 578.1 89,607 3 / 3

Nested Loop (cost=10,119.55..952,351.95 rows=155 width=30) (actual time=18.331..3,250.509 rows=89,607 loops=3)

33. 989.346 3,133.356 ↓ 505.6 4,045 3 / 3

Hash Join (cost=10,118.97..949,830.15 rows=8 width=30) (actual time=18.295..3,133.356 rows=4,045 loops=3)

  • Hash Cond: (fi_1.financialperiodid = fp_1.financialperiodid)
34. 2,136.823 2,136.823 ↑ 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.009..2,136.823 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. 1.141 7.187 ↓ 461.7 4,617 3 / 3

Hash (cost=10,118.85..10,118.85 rows=10 width=10) (actual time=7.187..7.187 rows=4,617 loops=3)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 263kB
36. 0.931 6.046 ↓ 461.7 4,617 3 / 3

Nested Loop (cost=1.16..10,118.85 rows=10 width=10) (actual time=0.100..6.046 rows=4,617 loops=3)

37. 0.062 0.315 ↑ 1.0 50 3 / 3

Nested Loop (cost=0.72..451.21 rows=50 width=8) (actual time=0.057..0.315 rows=50 loops=3)

38. 0.053 0.053 ↑ 1.0 50 3 / 3

Index Only Scan using uix_hpscompanyid_companyid on hpscompanyid hc_1 (cost=0.28..28.46 rows=50 width=4) (actual time=0.035..0.053 rows=50 loops=3)

  • Index Cond: ((companyid >= 1) AND (companyid <= 24,686))
  • 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.800 4.800 ↓ 1.8 92 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.014..0.096 rows=92 loops=150)

  • Index Cond: ((companyid = c_1.companyid) AND (periodtypeid = 4))
41. 101.117 101.117 ↑ 3.9 22 12,134 / 3

Index Only Scan using pk_ciqfininstancetocollection on ciqfininstancetocollection ic_1 (cost=0.57..314.38 rows=85 width=8) (actual time=0.009..0.025 rows=22 loops=12,134)

  • Index Cond: (financialinstanceid = fi_1.financialinstanceid)
  • Heap Fetches: 268,820
42. 358.427 358.427 ↓ 0.0 0 268,820 / 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=268,820)

  • Index Cond: (financialcollectionid = ic_1.financialcollectionid)
  • Filter: (currencyid = 0)
  • Rows Removed by Filter: 1
43. 181.584 21,406.736 ↑ 66.4 5 60,528 / 3

Nested Loop (cost=0.71..2,909.92 rows=332 width=28) (actual time=0.397..1.061 rows=5 loops=60,528)

44. 1,129.856 1,129.856 ↑ 1.0 332 60,528 / 3

Seq Scan on hpsmnemonic hm_1 (cost=0.00..8.24 rows=332 width=21) (actual time=0.002..0.056 rows=332 loops=60,528)

  • Filter: ((source)::text = 'ciqFinCollectionData'::text)
  • Rows Removed by Filter: 7
45. 20,095.296 20,095.296 ↓ 0.0 0 20,095,296 / 3

Index Scan using pk_ciqfincollectiondata on ciqfincollectiondata fd_1 (cost=0.71..8.73 rows=1 width=15) (actual time=0.003..0.003 rows=0 loops=20,095,296)

  • Index Cond: ((financialcollectionid = fc_1.financialcollectionid) AND (dataitemid = hm_1.dataitemid))
Planning time : 8.082 ms
Execution time : 110,516.745 ms