explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sx8D

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 5.759 ↓ 0.0 0 1

SetOp Except (cost=51,741.42..56,741.45 rows=20,000 width=220) (actual time=5.759..5.759 rows=0 loops=1)

  • Buffers: shared hit=254 read=3
2.          

CTE seed_records

3. 0.976 0.976 ↓ 0.0 0 1

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

  • Buffers: shared hit=254
4.          

CTE record_set

5. 0.000 0.985 ↓ 0.0 0 1

Unique (cost=342.21..354.71 rows=200 width=216) (actual time=0.985..0.985 rows=0 loops=1)

  • Buffers: shared hit=254
6. 0.006 0.985 ↓ 0.0 0 1

Sort (cost=342.21..346.37 rows=1,667 width=216) (actual time=0.985..0.985 rows=0 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: 25kB
  • Buffers: shared hit=254
7. 0.000 0.979 ↓ 0.0 0 1

Merge Left Join (cost=139.66..253.00 rows=1,667 width=216) (actual time=0.979..0.979 rows=0 loops=1)

  • Merge Cond: (seed_records.item_id = re.item_id)
  • Join Filter: (re.client_day > seed_records.client_day)
  • Buffers: shared hit=254
8. 0.003 0.979 ↓ 0.0 0 1

Sort (cost=69.83..72.33 rows=1,000 width=212) (actual time=0.979..0.979 rows=0 loops=1)

  • Sort Key: seed_records.item_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=254
9. 0.976 0.976 ↓ 0.0 0 1

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

  • Buffers: shared hit=254
10. 0.000 0.000 ↓ 0.0 0

Sort (cost=69.83..72.33 rows=1,000 width=20) (never executed)

  • Sort Key: re.item_id
11. 0.000 0.000 ↓ 0.0 0

CTE Scan on seed_records re (cost=0.00..20.00 rows=1,000 width=20) (never executed)

12.          

CTE load_targets

13. 0.985 0.985 ↓ 0.0 0 1

CTE Scan on record_set (cost=0.00..1,005.00 rows=200,000 width=216) (actual time=0.985..0.985 rows=0 loops=1)

  • Buffers: shared hit=254
14. 0.008 5.758 ↓ 0.0 0 1

Sort (cost=50,356.71..50,856.71 rows=200,001 width=220) (actual time=5.758..5.758 rows=0 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: 25kB
  • Buffers: shared hit=254 read=3
15. 0.000 5.750 ↓ 0.0 0 1

Append (cost=0.00..11,554.48 rows=200,001 width=220) (actual time=5.750..5.750 rows=0 loops=1)

  • Buffers: shared hit=254 read=3
16. 0.000 0.986 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1 (cost=0.00..6,000.00 rows=200,000 width=220) (actual time=0.986..0.986 rows=0 loops=1)

  • Buffers: shared hit=254
17. 0.986 0.986 ↓ 0.0 0 1

CTE Scan on load_targets (cost=0.00..4,000.00 rows=200,000 width=216) (actual time=0.986..0.986 rows=0 loops=1)

  • Buffers: shared hit=254
18. 0.002 4.764 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=5,004.45..5,554.48 rows=1 width=138) (actual time=4.764..4.764 rows=0 loops=1)

  • Buffers: shared read=3
19. 0.003 4.762 ↓ 0.0 0 1

Hash Join (cost=5,004.45..5,554.47 rows=1 width=134) (actual time=4.762..4.762 rows=0 loops=1)

  • Hash Cond: ((load_targets_1.item_id = targets.item_id) AND (load_targets_1.client_day = targets.client_day))
  • Buffers: shared read=3
20. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=5,000.00..5,200.00 rows=20,000 width=20) (never executed)

  • Group Key: load_targets_1.item_id, load_targets_1.client_day
21. 0.000 0.000 ↓ 0.0 0

CTE Scan on load_targets load_targets_1 (cost=0.00..4,000.00 rows=200,000 width=20) (never executed)

22. 0.000 4.759 ↓ 0.0 0 1

Hash (cost=4.44..4.44 rows=1 width=134) (actual time=4.759..4.759 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared read=3
23. 4.759 4.759 ↓ 0.0 0 1

Index Scan using idx_targets_target_type_code on targets (cost=0.42..4.44 rows=1 width=134) (actual time=4.759..4.759 rows=0 loops=1)

  • Index Cond: (target_type_code = 1)
  • Buffers: shared read=3