explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1BqA

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

Subquery Scan on x (cost=1,139,408.76..1,139,444.02 rows=8 width=568) (actual rows= loops=)

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

HashAggregate (cost=1,139,408.76..1,139,424.43 rows=1,567 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=843,075.99..1,139,385.25 rows=1,567 width=576) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=843,075.99..843,125.15 rows=1,229 width=77) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

WindowAgg (cost=843,075.99..843,112.86 rows=1,229 width=87) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Sort (cost=843,075.99..843,079.07 rows=1,229 width=60) (actual rows= loops=)

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

Nested Loop (cost=4.01..843,012.93 rows=1,229 width=60) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3.58..835,174.97 rows=996 width=56) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.87..19,468.47 rows=280 width=36) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.30..16,463.11 rows=375 width=30) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.72..10,473.64 rows=19 width=30) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

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

13. 0.000 0.000 ↓ 0.0

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

14. 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))
15. 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)
16. 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))
17. 0.000 0.000 ↓ 0.0

Index Scan using ix_ciqfininstance_financialperiodid on ciqfininstance fi (cost=0.56..15.19 rows=3 width=24) (actual rows= loops=)

  • Index Cond: (financialperiodid = fp.financialperiodid)
  • 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))
18. 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)
19. 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)
20. 0.000 0.000 ↓ 0.0

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

21. 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)
22. 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))
23. 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)
24. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=296,239.59..296,252.26 rows=338 width=52) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

WindowAgg (cost=296,239.59..296,248.88 rows=338 width=62) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Sort (cost=296,239.59..296,240.43 rows=338 width=54) (actual rows= loops=)

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

Nested Loop (cost=3.58..296,225.39 rows=338 width=54) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.87..19,467.83 rows=95 width=34) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.30..16,462.56 rows=375 width=30) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.72..10,473.29 rows=19 width=30) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

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

32. 0.000 0.000 ↓ 0.0

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

33. 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))
34. 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)
35. 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))
36. 0.000 0.000 ↓ 0.0

Index Scan using ix_ciqfininstance_financialperiodid on ciqfininstance fi_1 (cost=0.56..15.19 rows=3 width=24) (actual rows= loops=)

  • Index Cond: (financialperiodid = fp_1.financialperiodid)
  • 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))
37. 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)
38. 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)
39. 0.000 0.000 ↓ 0.0

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

40. 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)
41. 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))