explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4gJi

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

XN Window (cost=903,255,741,272,639.38..903,255,869,583,656.38 rows=3,948,031,288 width=374) (actual rows= loops=)

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

XN Sort (cost=903,255,741,272,639.38..903,255,751,142,717.62 rows=3,948,031,288 width=374) (actual rows= loops=)

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

XN Hash Right Join DS_DIST_BOTH (cost=88,201.71..902,255,111,986,333.25 rows=3,948,031,288 width=374) (actual rows= loops=)

  • Outer Dist Key: "inner".aeic
  • Inner Dist Key: s.aeic
  • Hash Cond: ((("outer".aeic)::text = ("inner".aeic)::text) AND ("outer".kpi_id = "inner".kpi_id) AND ((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".serie_ts))
4. 0.000 0.000 ↓ 0.0

XN Hash Join DS_DIST_ALL_NONE (cost=21.87..1,800,947,878.36 rows=7,929,208,548 width=144) (actual rows= loops=)

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

XN Partition Loop (cost=0.00..550,000,020.00 rows=10,000,000,000 width=307) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

XN Seq Scan PartitionInfo of ida_eap_prd_tbl_hist.pdc_snsr_wts wts (cost=0.00..20.00 rows=1 width=63) (actual rows= loops=)

  • Filter: ((("month")::text = '01'::text) AND (("year")::double precision = 2019::double precision) AND (subplan 0: (("datasource")::text = ($0)::text)))
7. 0.000 0.000 ↓ 0.0

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

8. 0.000 0.000 ↓ 0.0

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

  • Filter: ((tag_timestamp_utc < '2019-01-10 00:00:00'::timestamp without time zone) AND (tag_timestamp_utc >= '2019-01-09 00:00:00'::timestamp without time zone) AND (status = true))
9. 0.000 0.000 ↓ 0.0

XN Hash (cost=14.58..14.58 rows=1,458 width=46) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

XN Seq Scan on dim_unit_kpi uk (cost=0.00..14.58 rows=1,458 width=46) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

XN Hash (cost=50,388.48..50,388.48 rows=5,038,848 width=244) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

XN Seq Scan on dim_series s (cost=0.00..50,388.48 rows=5,038,848 width=244) (actual rows= loops=)