explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Anhp

Settings
# exclusive inclusive rows x rows loops node
1. 3,734.055 3,963.971 ↓ 0.0 0 1

Insert on anf (cost=301,081.81..304,697.83 rows=75,138 width=126) (actual time=3,963.971..3,963.971 rows=0 loops=1)

  • Conflict Resolution: UPDATE
  • Conflict Arbiter Indexes: anf_unique
  • Tuples Inserted: 0
  • Conflicting Tuples: 59440
2.          

CTE snap

3. 32.547 40.602 ↑ 1.3 58,894 1

Bitmap Heap Scan on snapshot (cost=1,950.74..276,284.42 rows=75,138 width=45) (actual time=8.991..40.602 rows=58,894 loops=1)

  • Recheck Cond: ((bucket = 10) AND (cache_date >= '2018-04-15 00:00:00'::timestamp without time zone))
  • Heap Blocks: exact=7688
4. 8.055 8.055 ↓ 1.6 117,781 1

Bitmap Index Scan on snapshot_bucket_cache_date (cost=0.00..1,931.95 rows=75,138 width=0) (actual time=8.055..8.055 rows=117,781 loops=1)

  • Index Cond: ((bucket = 10) AND (cache_date >= '2018-04-15 00:00:00'::timestamp without time zone))
5.          

CTE snap_target_date_range

6. 7.983 56.811 ↑ 1.0 1 1

Aggregate (cost=1,878.45..1,878.46 rows=1 width=16) (actual time=56.811..56.811 rows=1 loops=1)

7. 48.828 48.828 ↑ 1.3 58,894 1

CTE Scan on snap (cost=0.00..1,502.76 rows=75,138 width=8) (actual time=8.993..48.828 rows=58,894 loops=1)

8.          

Initplan (forInsert)

9. 56.814 56.814 ↑ 1.0 1 1

CTE Scan on snap_target_date_range (cost=0.00..0.02 rows=1 width=8) (actual time=56.813..56.814 rows=1 loops=1)

10. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on snap_target_date_range snap_target_date_range_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1)

11. 60.363 173.101 ↑ 1.3 59,440 1

Hash Full Join (cost=22,918.89..26,534.91 rows=75,138 width=126) (actual time=100.954..173.101 rows=59,440 loops=1)

  • Hash Cond: (((s.origin)::text = (source_aggregate.origin)::text) AND ((s.destination)::text = (source_aggregate.destination)::text) AND (s.capability = source_aggregate.capability) AND (s.execution_type = source_aggregate.execution_type) AND (s.target_date = s
12. 11.899 11.899 ↑ 1.3 58,894 1

CTE Scan on snap s (cost=0.00..1,502.76 rows=75,138 width=124) (actual time=0.002..11.899 rows=58,894 loops=1)

13. 14.452 100.839 ↓ 4.3 58,885 1

Hash (cost=22,611.43..22,611.43 rows=13,665 width=41) (actual time=100.839..100.839 rows=58,885 loops=1)

  • Buckets: 65536 (originally 16384) Batches: 1 (originally 1) Memory Usage: 5100kB
14. 21.469 86.387 ↓ 4.3 58,885 1

Bitmap Heap Scan on source_aggregate (cost=336.50..22,611.43 rows=13,665 width=41) (actual time=76.925..86.387 rows=58,885 loops=1)

  • Recheck Cond: ((target_date >= $2) AND (target_date <= $3))
  • Heap Blocks: exact=12122
15. 64.918 64.918 ↓ 8.6 117,770 1

Bitmap Index Scan on source_aggregate_target_date (cost=0.00..333.08 rows=13,665 width=0) (actual time=64.918..64.918 rows=117,770 loops=1)

  • Index Cond: ((target_date >= $2) AND (target_date <= $3))