explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GtZM : out_tmp_orig

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

Sort (cost=20,008,820,809.89..20,008,838,328.04 rows=7,007,258 width=177) (actual rows= loops=)

  • Sort Key: x.positionid, (min(x.asofdate)), (max(x.asofdate))
2. 0.000 0.000 ↓ 0.0

WindowAgg (cost=20,007,493,050.54..20,007,668,231.99 rows=7,007,258 width=177) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

Sort (cost=20,007,493,050.54..20,007,510,568.69 rows=7,007,258 width=164) (actual rows= loops=)

  • Sort Key: x.positionid, (max(x.asofdate)) DESC
4. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=20,005,754,709.57..20,006,367,844.64 rows=7,007,258 width=164) (actual rows= loops=)

  • Group Key: x.positionid, x.customerid, x.companyid, x.personid, x.positiontitle, x.positionevent, x.positionxid, x.companyofficer, x.flsacode, x.positionflsaprofile, x.positionflsa, x.position
5. 0.000 0.000 ↓ 0.0

Sort (cost=20,005,754,709.57..20,005,772,227.71 rows=7,007,258 width=160) (actual rows= loops=)

  • Sort Key: x.positionid, x.customerid, x.companyid, x.personid, x.positiontitle, x.positionevent, x.positionxid, x.companyofficer, x.flsacode, x.positionflsaprofile, x.positionflsa, x.pos
6. 0.000 0.000 ↓ 0.0

Subquery Scan on x (cost=20,004,205,236.04..20,004,643,189.67 rows=7,007,258 width=160) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

WindowAgg (cost=20,004,205,236.04..20,004,573,117.09 rows=7,007,258 width=177) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Sort (cost=20,004,205,236.04..20,004,222,754.19 rows=7,007,258 width=167) (actual rows= loops=)

  • Sort Key: pd.positionid, pp.personid, pd.effectivedate, ao.asofdate
9. 0.000 0.000 ↓ 0.0

WindowAgg (cost=20,002,939,884.98..20,003,080,030.14 rows=7,007,258 width=167) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Sort (cost=20,002,939,884.98..20,002,957,403.13 rows=7,007,258 width=159) (actual rows= loops=)

  • Sort Key: pd.positionid, ao.asofdate
11. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=20,000,000,046.26..20,001,828,365.08 rows=7,007,258 width=159) (actual rows= loops=)

  • Merge Cond: (pd.positionid = pp.positionid)
  • Join Filter: ((ao.asofdate >= pp.effectivedate) AND (ao.asofdate <= pp.enddate))
12. 0.000 0.000 ↓ 0.0

Nested Loop (cost=20,000,000,045.97..20,001,697,262.07 rows=7,007,258 width=138) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=10,000,000,045.67..10,001,442,301.76 rows=31,723 width=134) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Index Scan using ix_pos_posid on "position" pd (cost=0.29..1,522.41 rows=31,723 width=130) (actual rows= loops=)

  • Filter: ((companyid IS NOT NULL) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
15. 0.000 0.000 ↓ 0.0

Subquery Scan on cs (cost=45.39..45.41 rows=1 width=8) (actual rows= loops=)

  • Filter: (pd.companyid = cs.companyid)
16. 0.000 0.000 ↓ 0.0

Unique (cost=45.39..45.39 rows=1 width=8) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Sort (cost=45.39..45.39 rows=1 width=8) (actual rows= loops=)

  • Sort Key: customer_struct.customerid
18. 0.000 0.000 ↓ 0.0

Seq Scan on customer_struct (cost=0.00..45.38 rows=1 width=8) (actual rows= loops=)

  • Filter: ((companyid = pd.companyid) AND (CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT
19. 0.000 0.000 ↓ 0.0

Index Only Scan using ix_asofdate on asofdate ao (cost=0.30..5.83 rows=221 width=4) (actual rows= loops=)

  • Index Cond: ((asofdate >= pd.effectivedate) AND (asofdate <= pd.enddate) AND (asofdate >= make_date((date_part('year'::text, (CURRENT_DATE
20. 0.000 0.000 ↓ 0.0

Materialize (cost=0.29..1,174.98 rows=24,678 width=25) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Index Scan using ix_perspos_posid on person_position pp (cost=0.29..1,113.29 rows=24,678 width=25) (actual rows= loops=)

  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))