explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IXhH

Settings
# exclusive inclusive rows x rows loops node
1. 0.020 21,643.333 ↑ 6.5 31 1

Sort (cost=1,562,911.93..1,562,912.43 rows=200 width=16) (actual time=21,643.331..21,643.333 rows=31 loops=1)

  • Sort Key: cfs2_hindcast.valid_t
  • Sort Method: quicksort Memory: 26kB
2. 872.629 21,643.313 ↑ 6.5 31 1

HashAggregate (cost=1,562,901.78..1,562,904.28 rows=200 width=16) (actual time=21,643.308..21,643.313 rows=31 loops=1)

  • Group Key: cfs2_hindcast.valid_t
3. 906.881 20,770.684 ↑ 1.1 4,559,294 1

Unique (cost=1,402,717.08..1,488,970.38 rows=4,928,760 width=140) (actual time=17,862.440..20,770.684 rows=4,559,294 loops=1)

4. 18,890.537 19,863.803 ↑ 1.1 4,559,294 1

Sort (cost=1,402,717.08..1,415,038.98 rows=4,928,760 width=140) (actual time=17,862.434..19,863.803 rows=4,559,294 loops=1)

  • Sort Key: px.geo_id, px.cfs2_hindcast_id, cfs2_hindcast.valid_t, cfs2_hindcast.sds_id, cfs2_hindcast.total_precipitation_gmtday, cfs2_hindcast.sds_id
  • Sort Method: external merge Disk: 303192kB
5. 235.800 973.266 ↑ 1.1 4,559,294 1

Append (cost=9,478.17..147,263.57 rows=4,928,760 width=140) (actual time=64.226..973.266 rows=4,559,294 loops=1)

6. 620.647 710.180 ↑ 1.1 4,559,294 1

Hash Join (cost=9,478.17..82,894.10 rows=4,925,010 width=50) (actual time=64.225..710.180 rows=4,559,294 loops=1)

  • Hash Cond: ((cfs2_hindcast.grid_id)::text = (px.cfs2_hindcast_id)::text)
7. 26.428 26.428 ↑ 1.1 2,294 1

Seq Scan on cfs2_hindcast (cost=0.00..5,913.42 rows=2,442 width=34) (actual time=0.493..26.428 rows=2,294 loops=1)

  • Filter: ((valid_t >= '2016-12-01 00:00:00'::timestamp without time zone) AND (valid_t <= '2016-12-31 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 213934
8. 34.166 63.105 ↑ 1.0 147,074 1

Hash (cost=6,777.74..6,777.74 rows=147,074 width=18) (actual time=63.105..63.105 rows=147,074 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 2389kB
9. 28.939 28.939 ↑ 1.0 147,074 1

Seq Scan on project_tbl_xref px (cost=0.00..6,777.74 rows=147,074 width=18) (actual time=0.007..28.939 rows=147,074 loops=1)

10. 0.000 27.286 ↓ 0.0 0 1

Nested Loop (cost=1,190.96..15,081.87 rows=3,750 width=50) (actual time=27.286..27.286 rows=0 loops=1)

11. 4.361 27.365 ↓ 0.0 0 1

Gather (cost=1,000.00..9,477.05 rows=1 width=34) (actual time=27.283..27.365 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 23.004 23.004 ↓ 0.0 0 3

Parallel Seq Scan on cfs_hindcast (cost=0.00..8,476.95 rows=1 width=34) (actual time=23.004..23.004 rows=0 loops=3)

  • Filter: ((valid_t >= '2016-12-01 00:00:00'::timestamp without time zone) AND (valid_t <= '2016-12-31 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 151944
13. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on project_tbl_xref px_1 (cost=190.96..5,560.25 rows=4,457 width=18) (never executed)

  • Recheck Cond: ((cfs_hindcast_id)::text = (cfs_hindcast.grid_id)::text)
14. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on project_tbl_xref_cfs_hindcast_id_idx (cost=0.00..189.85 rows=4,457 width=0) (never executed)

  • Index Cond: ((cfs_hindcast_id)::text = (cfs_hindcast.grid_id)::text)