explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rpkJ

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

XN Window (cost=1,450,204,938,129.17..1,450,204,938,129.21 rows=1 width=171) (actual rows= loops=)

  • Partition: s.aeic, s.kpi_id
  • Order: s.serie_ts, w.tag_timestamp_utc
2. 0.000 0.000 ↓ 0.0

XN Sort (cost=1,450,204,938,129.17..1,450,204,938,129.18 rows=1 width=171) (actual rows= loops=)

  • Sort Key: s.aeic, s.kpi_id, s.serie_ts, w.tag_timestamp_utc
3. 0.000 0.000 ↓ 0.0

XN Network (cost=987,843.30..450,204,938,129.16 rows=1 width=171) (actual rows= loops=)

  • Distribute
4. 0.000 0.000 ↓ 0.0

XN Hash Right Join DS_DIST_BOTH (cost=987,843.30..450,204,938,129.16 rows=1 width=171) (actual rows= loops=)

  • Outer Dist Key: (date_trunc('hour'::text, "outer".tag_timestamp_utc) + ((((pgdate_part('min'::text, "outer".tag_timestamp_utc))::integer / 10))::double precision * '00:10:00'::interval))
  • Inner Dist Key: s.serie_ts
  • Hash Cond: (("outer"."?column6?" = "inner".serie_ts) AND (("outer".aeic)::text = ("inner".aeic)::text) AND ("outer".kpi_id = "inner".kpi_id))
5. 0.000 0.000 ↓ 0.0

XN Hash Join DS_DIST_ALL_NONE (cost=8,438.66..450,202,308,724.08 rows=4 width=144) (actual rows= loops=)

  • Hash Cond: ((("outer".id)::text = ("inner".aeic)::text) AND ("outer".kpi_id = "inner".kpi_id) AND (("outer"."month")::text = to_char(("inner".fact_dt)::timestamp without time zone, 'MM'::text)) AND (("outer"."year")::double precision = pgdate_part('year'::text, ("inner".fact_dt)::timestamp without time zone)) AND ("outer".fact_id = "inner".fact_id))
  • Join Filter: (("inner".from_ts <= "outer".tag_timestamp_utc) AND ("inner".to_ts > "outer".tag_timestamp_utc) AND ("inner".to_ts < "outer".mapping_end) AND ("inner".from_ts >= "outer".mapping_start))
  • Remarks: Derives subplan 1
6. 0.000 0.000 ↓ 0.0

XN Hash Join DS_BCAST_INNER (cost=5,413.13..450,200,768,287.87 rows=12,550,240 width=176) (actual rows= loops=)

  • Hash Cond: ((("outer".tag_name)::text = ("inner".tag_name)::text) AND (("outer"."datasource")::text = ("inner"."datasource")::text))
  • Remarks: Derives subplan 0
7. 0.000 0.000 ↓ 0.0

XN Partition Loop (cost=0.00..200,200,000,010.00 rows=10,000,000,000,000 width=319) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

XN Seq Scan PartitionInfo of ida_eap_prd_tbl_hist.pdc_snsr_wts w (cost=0.00..10.00 rows=1,000 width=75) (actual rows= loops=)

  • Filter: ((subplan 0: (($2)::text = ("datasource")::text)) AND (subplan 1: (("month")::text = to_char(($12)::timestamp without time zone, 'MM'::text)) AND (("year")::double precision = pgdate_part('year'::text, ($12)::timestamp without time zone))))
9. 0.000 0.000 ↓ 0.0

XN S3 Query Scan w (cost=0.00..200,000,000.00 rows=10,000,000,000 width=244) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

S3 Seq Scan ida_eap_prd_tbl_hist.pdc_snsr_wts w location:"s3://den-prd-fra-mosaic-archive/ORC/wts" format:ORC_FILE (cost=0.00..100,000,000.00 rows=10,000,000,000 width=244) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

XN Hash (cost=5,413.13..5,413.13 rows=1 width=65) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

XN Hash Join DS_DIST_ALL_NONE (cost=0.15..5,413.13 rows=1 width=65) (actual rows= loops=)

  • Hash Cond: (("outer".gpn)::text = ("inner".kpi_name)::text)
13. 0.000 0.000 ↓ 0.0

XN Seq Scan on pdc_gpn_map g (cost=0.00..5,407.70 rows=421 width=74) (actual rows= loops=)

  • Filter: ('000710'::text = (id)::text)
14. 0.000 0.000 ↓ 0.0

XN Hash (cost=0.15..0.15 rows=1 width=26) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

XN Seq Scan on dim_kpi_v2 k (cost=0.00..0.15 rows=1 width=26) (actual rows= loops=)

  • Filter: ((kpi_id = 12) AND (fact_id = 7))
16. 0.000 0.000 ↓ 0.0

XN Hash (cost=3,025.51..3,025.51 rows=1 width=38) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

XN Seq Scan on asset_missing_series s (cost=0.00..3,025.51 rows=1 width=38) (actual rows= loops=)

  • Filter: ((kpi_id = 12) AND ((aeic)::text = '000710'::text) AND (fact_dt = '2018-01-01'::date) AND (fact_id = 7))
18. 0.000 0.000 ↓ 0.0

XN Hash (cost=979,404.64..979,404.64 rows=1 width=41) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

XN Hash Join DS_DIST_ALL_NONE (cost=973,972.34..979,404.64 rows=1 width=41) (actual rows= loops=)

  • Hash Cond: (("outer".kpi_id = "inner".kpi_id) AND (("outer".aeic)::text = ("inner".aeic)::text) AND ("outer".fact_id = "inner".fact_id))
  • Join Filter: (("inner".serie_ts <= "outer".to_ts) AND ("inner".serie_ts >= "outer".from_ts))
20. 0.000 0.000 ↓ 0.0

XN Hash Join DS_DIST_ALL_NONE (cost=3,025.68..8,438.70 rows=1 width=42) (actual rows= loops=)

  • Hash Cond: ((("outer".id)::text = ("inner".aeic)::text) AND ("outer".kpi_id = "inner".kpi_id) AND ("outer".fact_id = "inner".fact_id))
  • Join Filter: (("inner".to_ts < "outer".mapping_end) AND ("inner".from_ts >= "outer".mapping_start))
21. 0.000 0.000 ↓ 0.0

XN Hash Join DS_DIST_ALL_NONE (cost=0.15..5,413.13 rows=1 width=34) (actual rows= loops=)

  • Hash Cond: (("outer".gpn)::text = ("inner".kpi_name)::text)
22. 0.000 0.000 ↓ 0.0

XN Seq Scan on pdc_gpn_map g (cost=0.00..5,407.70 rows=421 width=43) (actual rows= loops=)

  • Filter: ('000710'::text = (id)::text)
23. 0.000 0.000 ↓ 0.0

XN Hash (cost=0.15..0.15 rows=1 width=26) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

XN Seq Scan on dim_kpi_v2 k (cost=0.00..0.15 rows=1 width=26) (actual rows= loops=)

  • Filter: ((kpi_id = 12) AND (fact_id = 7))
25. 0.000 0.000 ↓ 0.0

XN Hash (cost=3,025.51..3,025.51 rows=1 width=34) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

XN Seq Scan on asset_missing_series volt_dt_1 (cost=0.00..3,025.51 rows=1 width=34) (actual rows= loops=)

  • Filter: ((kpi_id = 12) AND ((aeic)::text = '000710'::text) AND (fact_dt = '2018-01-01'::date) AND (fact_id = 7))
27. 0.000 0.000 ↓ 0.0

XN Hash (cost=970,943.46..970,943.46 rows=428 width=45) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

XN Seq Scan on asset_facts_series s (cost=0.00..970,943.46 rows=428 width=45) (actual rows= loops=)

  • Filter: ((kpi_id = 12) AND ('000710'::text = (aeic)::text) AND (fact_id = 7))