explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2AMu

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Subquery Scan on x (cost=2,361,781.52..2,361,816.37 rows=8 width=568) (actual rows= loops=)

  • Filter: (x.rownum = 1)
2. 0.000 0.000 ↓ 0.0

HashAggregate (cost=2,361,781.52..2,361,797.01 rows=1,549 width=576) (actual rows= loops=)

  • Group Key: "*SELECT* 1".mnemonic, "*SELECT* 1".companyid, "*SELECT* 1".periodenddate, "*SELECT* 1".filingdate, "*SELECT* 1".dataitemvalue, "*SELECT* 1".rownum
3. 0.000 0.000 ↓ 0.0

Append (cost=1,293,229.38..2,361,758.29 rows=1,549 width=576) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=1,293,229.38..1,293,277.98 rows=1,215 width=77) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

WindowAgg (cost=1,293,229.38..1,293,265.83 rows=1,215 width=87) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Sort (cost=1,293,229.38..1,293,232.41 rows=1,215 width=60) (actual rows= loops=)

  • Sort Key: hm.mnemonic, fi.financialperiodid, fi.filingdate DESC, fd.financialcollectionid DESC
7. 0.000 0.000 ↓ 0.0

Gather (cost=11,323.87..1,293,167.13 rows=1,215 width=60) (actual rows= loops=)

  • Workers Planned: 2
8. 0.000 0.000 ↓ 0.0

Nested Loop (cost=10,323.87..1,292,045.63 rows=506 width=60) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Nested Loop (cost=10,323.44..1,288,819.16 rows=410 width=56) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Nested Loop (cost=10,322.73..953,796.85 rows=115 width=36) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Nested Loop (cost=10,322.15..952,554.63 rows=155 width=30) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Hash Join (cost=10,321.58..950,032.75 rows=8 width=30) (actual rows= loops=)

  • Hash Cond: (fi.financialperiodid = fp.financialperiodid)
13. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on ciqfininstance fi (cost=0.00..886,427.20 rows=14,209,038 width=24) (actual rows= loops=)

  • 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))
14. 0.000 0.000 ↓ 0.0

Hash (cost=10,321.45..10,321.45 rows=10 width=10) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.16..10,321.45 rows=10 width=10) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.72..460.11 rows=51 width=8) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

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

  • Index Cond: ((companyid >= 28,312) AND (companyid <= 30,345))
18. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (companyid = hc.companyid)
19. 0.000 0.000 ↓ 0.0

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

  • Index Cond: ((companyid = c.companyid) AND (periodtypeid = 4))
20. 0.000 0.000 ↓ 0.0

Index Only Scan using pk_ciqfininstancetocollection on ciqfininstancetocollection ic (cost=0.57..314.39 rows=85 width=8) (actual rows= loops=)

  • Index Cond: (financialinstanceid = fi.financialinstanceid)
21. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (financialcollectionid = ic.financialcollectionid)
  • Filter: (currencyid <> 0)
22. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.71..2,909.92 rows=332 width=28) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Seq Scan on hpsmnemonic hm (cost=0.00..8.24 rows=332 width=21) (actual rows= loops=)

  • Filter: ((source)::text = 'ciqFinCollectionData'::text)
24. 0.000 0.000 ↓ 0.0

Index Scan using pk_ciqfincollectiondata on ciqfincollectiondata fd (cost=0.71..8.73 rows=1 width=15) (actual rows= loops=)

  • Index Cond: ((financialcollectionid = fc.financialcollectionid) AND (dataitemid = hm.dataitemid))
25. 0.000 0.000 ↓ 0.0

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

  • Index Cond: ((currencyid = fc.currencyid) AND (pricedate = fi.periodenddate))
  • Filter: (latestsnapflag = 1)
26. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=1,068,460.04..1,068,472.56 rows=334 width=52) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

WindowAgg (cost=1,068,460.04..1,068,469.22 rows=334 width=62) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Sort (cost=1,068,460.04..1,068,460.87 rows=334 width=54) (actual rows= loops=)

  • Sort Key: hm_1.mnemonic, fi_1.financialperiodid, fi_1.filingdate DESC, fd_1.financialcollectionid DESC
29. 0.000 0.000 ↓ 0.0

Gather (cost=11,323.09..1,068,446.04 rows=334 width=54) (actual rows= loops=)

  • Workers Planned: 2
30. 0.000 0.000 ↓ 0.0

Nested Loop (cost=10,323.09..1,067,412.64 rows=139 width=54) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Nested Loop (cost=10,322.38..953,796.38 rows=39 width=34) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Nested Loop (cost=10,321.80..952,554.20 rows=155 width=30) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Hash Join (cost=10,321.23..950,032.40 rows=8 width=30) (actual rows= loops=)

  • Hash Cond: (fi_1.financialperiodid = fp_1.financialperiodid)
34. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on ciqfininstance fi_1 (cost=0.00..886,427.20 rows=14,209,038 width=24) (actual rows= loops=)

  • 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))
35. 0.000 0.000 ↓ 0.0

Hash (cost=10,321.10..10,321.10 rows=10 width=10) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.16..10,321.10 rows=10 width=10) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.72..460.11 rows=51 width=8) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

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

  • Index Cond: ((companyid >= 28,312) AND (companyid <= 30,345))
39. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (companyid = hc_1.companyid)
40. 0.000 0.000 ↓ 0.0

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

  • Index Cond: ((companyid = c_1.companyid) AND (periodtypeid = 4))
41. 0.000 0.000 ↓ 0.0

Index Only Scan using pk_ciqfininstancetocollection on ciqfininstancetocollection ic_1 (cost=0.57..314.38 rows=85 width=8) (actual rows= loops=)

  • Index Cond: (financialinstanceid = fi_1.financialinstanceid)
42. 0.000 0.000 ↓ 0.0

Index Scan using pk_ciqfincollection on ciqfincollection fc_1 (cost=0.57..8.01 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (financialcollectionid = ic_1.financialcollectionid)
  • Filter: (currencyid = 0)
43. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.71..2,909.92 rows=332 width=28) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Seq Scan on hpsmnemonic hm_1 (cost=0.00..8.24 rows=332 width=21) (actual rows= loops=)

  • Filter: ((source)::text = 'ciqFinCollectionData'::text)
45. 0.000 0.000 ↓ 0.0

Index Scan using pk_ciqfincollectiondata on ciqfincollectiondata fd_1 (cost=0.71..8.73 rows=1 width=15) (actual rows= loops=)

  • Index Cond: ((financialcollectionid = fc_1.financialcollectionid) AND (dataitemid = hm_1.dataitemid))