explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fMU4 : targets_validation

Settings
# exclusive inclusive rows x rows loops node
1. 8.436 51.117 ↓ 0.0 0 1

SetOp Except (cost=52,866.91..57,929.16 rows=20,000 width=220) (actual time=51.117..51.117 rows=0 loops=1)

  • Buffers: shared hit=373
2.          

CTE seed_records

3. 4.719 4.719 ↑ 5.4 184 1

Function Scan on crosstab (cost=0.00..25.00 rows=1,000 width=212) (actual time=4.395..4.719 rows=184 loops=1)

  • Buffers: shared hit=174
4.          

CTE record_set

5. 0.042 5.381 ↑ 1.1 184 1

Unique (cost=342.21..354.71 rows=200 width=216) (actual time=5.312..5.381 rows=184 loops=1)

  • Buffers: shared hit=174
6. 0.098 5.339 ↑ 9.1 184 1

Sort (cost=342.21..346.37 rows=1,667 width=216) (actual time=5.311..5.339 rows=184 loops=1)

  • Sort Key: seed_records.item_id, seed_records.client_day, ((((date_trunc('month'::text, (seed_records.client_day)::timestamp with time zone) + '1 mon'::interval) - '1 day'::interval))::date)
  • Sort Method: quicksort Memory: 45kB
  • Buffers: shared hit=174
7. 0.302 5.241 ↑ 9.1 184 1

Merge Left Join (cost=139.66..253.00 rows=1,667 width=216) (actual time=4.884..5.241 rows=184 loops=1)

  • Merge Cond: (seed_records.item_id = re.item_id)
  • Join Filter: (re.client_day > seed_records.client_day)
  • Rows Removed by Join Filter: 271
  • Buffers: shared hit=174
8. 0.047 4.842 ↑ 5.4 184 1

Sort (cost=69.83..72.33 rows=1,000 width=212) (actual time=4.822..4.842 rows=184 loops=1)

  • Sort Key: seed_records.item_id
  • Sort Method: quicksort Memory: 39kB
  • Buffers: shared hit=174
9. 4.795 4.795 ↑ 5.4 184 1

CTE Scan on seed_records (cost=0.00..20.00 rows=1,000 width=212) (actual time=4.397..4.795 rows=184 loops=1)

  • Buffers: shared hit=174
10. 0.067 0.097 ↑ 2.8 357 1

Sort (cost=69.83..72.33 rows=1,000 width=20) (actual time=0.053..0.097 rows=357 loops=1)

  • Sort Key: re.item_id
  • Sort Method: quicksort Memory: 39kB
11. 0.030 0.030 ↑ 5.4 184 1

CTE Scan on seed_records re (cost=0.00..20.00 rows=1,000 width=20) (actual time=0.001..0.030 rows=184 loops=1)

12.          

CTE load_targets

13. 7.594 7.594 ↑ 35.1 5,704 1

CTE Scan on record_set (cost=0.00..1,005.00 rows=200,000 width=216) (actual time=5.320..7.594 rows=5,704 loops=1)

  • Buffers: shared hit=174
14. 19.423 42.681 ↑ 17.7 11,408 1

Sort (cost=51,482.20..51,988.43 rows=202,490 width=220) (actual time=41.196..42.681 rows=11,408 loops=1)

  • Sort Key: "*SELECT* 1".item_id, "*SELECT* 1".client_day, "*SELECT* 1".atc_t_50, "*SELECT* 1".atc_t_51, "*SELECT* 1".atc_t_79, "*SELECT* 1".atc_t_10013, "*SELECT* 1".atc_t_10015, "*SELECT* 1".atc_t_10022, "*SELECT* 1".target_type_code
  • Sort Method: quicksort Memory: 1,938kB
  • Buffers: shared hit=373
15. 1.265 23.258 ↑ 17.7 11,408 1

Append (cost=0.00..12,176.75 rows=202,490 width=220) (actual time=5.322..23.258 rows=11,408 loops=1)

  • Buffers: shared hit=373
16. 1.096 10.927 ↑ 35.1 5,704 1

Subquery Scan on *SELECT* 1 (cost=0.00..6,000.00 rows=200,000 width=220) (actual time=5.322..10.927 rows=5,704 loops=1)

  • Buffers: shared hit=174
17. 9.831 9.831 ↑ 35.1 5,704 1

CTE Scan on load_targets (cost=0.00..4,000.00 rows=200,000 width=216) (actual time=5.321..9.831 rows=5,704 loops=1)

  • Buffers: shared hit=174
18. 0.824 11.066 ↓ 2.3 5,704 1

Subquery Scan on *SELECT* 2 (cost=5,700.00..6,176.75 rows=2,490 width=141) (actual time=5.258..11.066 rows=5,704 loops=1)

  • Buffers: shared hit=199
19. 3.111 10.242 ↓ 2.3 5,704 1

Hash Join (cost=5,700.00..6,151.85 rows=2,490 width=137) (actual time=5.258..10.242 rows=5,704 loops=1)

  • Hash Cond: ((targets.item_id = load_targets_1.item_id) AND (targets.client_day = load_targets_1.client_day))
  • Buffers: shared hit=199
20. 2.346 2.346 ↑ 1.2 8,344 1

Seq Scan on targets (cost=0.00..363.31 rows=9,961 width=137) (actual time=0.204..2.346 rows=8,344 loops=1)

  • Filter: (target_type_code = 1)
  • Rows Removed by Filter: 2,667
  • Buffers: shared hit=199
21. 1.157 4.785 ↑ 3.5 5,704 1

Hash (cost=5,400.00..5,400.00 rows=20,000 width=20) (actual time=4.785..4.785 rows=5,704 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 546kB
22. 2.960 3.628 ↑ 3.5 5,704 1

HashAggregate (cost=5,000.00..5,200.00 rows=20,000 width=20) (actual time=2.743..3.628 rows=5,704 loops=1)

  • Group Key: load_targets_1.item_id, load_targets_1.client_day
23. 0.668 0.668 ↑ 35.1 5,704 1

CTE Scan on load_targets load_targets_1 (cost=0.00..4,000.00 rows=200,000 width=20) (actual time=0.001..0.668 rows=5,704 loops=1)