explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qdy

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

XN Hash Left Join DS_DIST_ALL_NONE (cost=2,167,039,150,468.30..2,167,039,150,534.62 rows=1,000 width=229) (actual rows= loops=)

  • Hash Cond: (checksum("outer".unitofmeasure) = "inner".uom_id)
2. 0.000 0.000 ↓ 0.0

XN Subquery Scan d3 (cost=2,167,039,150,466.49..2,167,039,150,500.56 rows=1,000 width=204) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

XN HashAggregate (cost=2,167,039,150,466.49..2,167,039,150,490.56 rows=1,000 width=204) (actual rows= loops=)

  • Filter: (avg(tag_value) IS NOT NULL)
4. 0.000 0.000 ↓ 0.0

XN Subquery Scan d2 (cost=2,167,039,146,976.63..2,167,039,149,263.09 rows=48,136 width=204) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

XN Window (cost=2,167,039,146,976.63..2,167,039,148,781.73 rows=48,136 width=179) (actual rows= loops=)

  • Partition: "ts".event_key, "ts".kpi_id
  • Order: "ts".serie_ts, wts.tag_timestamp_utc
6. 0.000 0.000 ↓ 0.0

XN Sort (cost=2,167,039,146,976.63..2,167,039,147,096.97 rows=48,136 width=179) (actual rows= loops=)

  • Sort Key: "ts".event_key, "ts".kpi_id, "ts".serie_ts, wts.tag_timestamp_utc
7. 0.000 0.000 ↓ 0.0

XN Network (cost=361.31..1,167,039,143,232.89 rows=48,136 width=179) (actual rows= loops=)

  • Distribute
8. 0.000 0.000 ↓ 0.0

XN Hash Right Join DS_DIST_BOTH (cost=361.31..1,167,039,143,232.89 rows=48,136 width=179) (actual rows= loops=)

  • Outer Dist Key: date_trunc('sec'::text, "outer".tag_timestamp_utc)
  • Inner Dist Key: "ts".serie_ts
  • Hash Cond: (("outer"."?column6?" = "inner".serie_ts) AND ("outer".event_key = "inner".event_key) AND ("outer".kpi_id = "inner".kpi_id))
9. 0.000 0.000 ↓ 0.0

XN Hash Join DS_DIST_ALL_NONE (cost=0.02..1,125,225,000,103.78 rows=48,136 width=138) (actual rows= loops=)

  • Hash Cond: ((("outer".tag_name)::text = ("inner".tag_name)::text) AND (("outer"."datasource")::text = ("inner"."datasource")::text) AND (("outer"."month")::text = to_char("inner".series_strt_ts, 'MM'::text)) AND (("outer"."year")::double precision = pgdate_part('year'::text, "inner".series_strt_ts)))
  • Join Filter: (("inner".series_strt_ts <= "outer".tag_timestamp_utc) AND ("inner".snsr_end_ts > "outer".tag_timestamp_utc))
  • Remarks: Derives subplan 11
10. 0.000 0.000 ↓ 0.0

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

11. 0.000 0.000 ↓ 0.0

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

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

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

13. 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..125,000,000.00 rows=10,000,000,000 width=244) (actual rows= loops=)

  • Filter: (status = true)
14. 0.000 0.000 ↓ 0.0

XN Hash (cost=0.01..0.01 rows=1 width=58) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

XN Seq Scan on evnt_kpi kpi (cost=0.00..0.01 rows=1 width=58) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

XN Hash (cost=206.45..206.45 rows=20,645 width=49) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

XN Seq Scan on evnt_kpi_ts "ts" (cost=0.00..206.45 rows=20,645 width=49) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

XN Hash (cost=1.45..1.45 rows=145 width=29) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

XN Seq Scan on uom_cnvs_map uom (cost=0.00..1.45 rows=145 width=29) (actual rows= loops=)