explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0flX

Settings
# exclusive inclusive rows x rows loops node
1. 333.271 9,044.420 ↑ 3.9 236,343 1

Unique (cost=932,179.08..966,529.79 rows=916,019 width=851) (actual time=8,580.975..9,044.420 rows=236,343 loops=1)

2.          

CTE events

3. 25.481 6,547.063 ↑ 3.2 236,343 1

Append (cost=0.00..423,504.07 rows=756,925 width=218) (actual time=0.013..6,547.063 rows=236,343 loops=1)

4. 30.588 30.588 ↑ 1.0 67,548 1

Seq Scan on events_archive (cost=0.00..3,283.48 rows=67,548 width=263) (actual time=0.012..30.588 rows=67,548 loops=1)

5. 29.665 6,490.994 ↑ 4.1 168,795 1

Subquery Scan on "*SELECT* 2" (cost=375,411.08..420,220.59 rows=689,377 width=213) (actual time=5,046.010..6,490.994 rows=168,795 loops=1)

6. 1,017.126 6,461.329 ↑ 4.1 168,795 1

GroupAggregate (cost=375,411.08..413,326.82 rows=689,377 width=221) (actual time=5,046.008..6,461.329 rows=168,795 loops=1)

  • Group Key: o.event_uid, o.chain_hash, o.date_from, o.organization_id, o.init_point_id, o.is_imported, o.is_fbs, o.autorepeat_hash
7. 2,004.045 5,444.203 ↑ 1.0 675,269 1

Sort (cost=375,411.08..377,134.52 rows=689,377 width=157) (actual time=5,045.947..5,444.203 rows=675,269 loops=1)

  • Sort Key: o.event_uid, o.chain_hash, o.date_from, o.organization_id, o.init_point_id, o.is_imported, o.is_fbs, o.autorepeat_hash
  • Sort Method: external merge Disk: 117,648kB
8. 207.735 3,440.158 ↑ 1.0 675,269 1

Hash Join (cost=4,529.35..254,361.48 rows=689,377 width=157) (actual time=35.933..3,440.158 rows=675,269 loops=1)

  • Hash Cond: (o.point_to_id = pt.id)
9. 247.368 3,212.602 ↑ 1.0 675,269 1

Hash Join (cost=2,264.68..250,287.03 rows=689,377 width=157) (actual time=15.713..3,212.602 rows=675,269 loops=1)

  • Hash Cond: (o.point_from_id = p.id)
10. 2,949.933 2,949.933 ↑ 1.0 675,269 1

Seq Scan on org_schedule_calendar o (cost=0.00..246,212.59 rows=689,377 width=157) (actual time=0.015..2,949.933 rows=675,269 loops=1)

  • Filter: (is_active AND (event_uid IS NOT NULL) AND (init_point_id IS NOT NULL) AND (date_from >= (CURRENT_TIMESTAMP - '30 days'::interval)))
  • Rows Removed by Filter: 1,680,500
11. 6.472 15.301 ↑ 1.4 35,720 1

Hash (cost=1,639.30..1,639.30 rows=50,030 width=8) (actual time=15.301..15.301 rows=35,720 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 1,908kB
12. 8.829 8.829 ↑ 1.4 35,720 1

Seq Scan on valid_point p (cost=0.00..1,639.30 rows=50,030 width=8) (actual time=0.006..8.829 rows=35,720 loops=1)

  • Filter: (NOT is_deleted)
13. 6.363 19.821 ↑ 1.4 35,720 1

Hash (cost=1,639.30..1,639.30 rows=50,030 width=8) (actual time=19.821..19.821 rows=35,720 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 1,908kB
14. 13.458 13.458 ↑ 1.4 35,720 1

Seq Scan on valid_point pt (cost=0.00..1,639.30 rows=50,030 width=8) (actual time=0.009..13.458 rows=35,720 loops=1)

  • Filter: (NOT is_deleted)
15. 1,356.629 8,711.149 ↑ 3.5 264,071 1

Sort (cost=508,675.01..510,965.06 rows=916,019 width=851) (actual time=8,580.973..8,711.149 rows=264,071 loops=1)

  • Sort Key: t.date, t.uid, t.point_from_ids, t.point_to_ids, t.organization_id, t.created_at, t.updated_at, t.init_point_id, t.chain_hash, (COALESCE(em.meta, '{}'::jsonb)), t.is_imported, em.parent_chain_hash, t.autorepeat_hash, t.is_fbs
  • Sort Method: external merge Disk: 87,528kB
16. 161.243 7,354.520 ↑ 3.5 264,071 1

Hash Left Join (cost=7,868.11..73,562.65 rows=916,019 width=851) (actual time=94.705..7,354.520 rows=264,071 loops=1)

  • Hash Cond: ((t.uid)::text = (eo.event_uid)::text)
17. 142.183 7,150.081 ↑ 2.1 236,343 1

Hash Left Join (cost=3,164.60..21,663.45 rows=507,140 width=843) (actual time=50.728..7,150.081 rows=236,343 loops=1)

  • Hash Cond: ((t.uid)::text = (em.event_uid)::text)
18. 115.503 6,958.200 ↑ 2.1 236,343 1

Hash Join (cost=14.06..17,181.59 rows=507,140 width=786) (actual time=0.155..6,958.200 rows=236,343 loops=1)

  • Hash Cond: (t.organization_id = org.id)
19. 6,842.577 6,842.577 ↑ 3.2 236,343 1

CTE Scan on events t (cost=0.00..15,138.50 rows=756,925 width=786) (actual time=0.014..6,842.577 rows=236,343 loops=1)

20. 0.029 0.120 ↓ 1.0 137 1

Hash (cost=12.38..12.38 rows=134 width=8) (actual time=0.120..0.120 rows=137 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
21. 0.091 0.091 ↓ 1.0 137 1

Seq Scan on organization org (cost=0.00..12.38 rows=134 width=8) (actual time=0.010..0.091 rows=137 loops=1)

  • Filter: (NOT is_deleted)
  • Rows Removed by Filter: 4
22. 24.891 49.698 ↑ 1.0 80,102 1

Hash (cost=2,148.02..2,148.02 rows=80,202 width=90) (actual time=49.698..49.698 rows=80,102 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 8,264kB
23. 24.807 24.807 ↑ 1.0 80,102 1

Seq Scan on event_meta em (cost=0.00..2,148.02 rows=80,202 width=90) (actual time=0.008..24.807 rows=80,102 loops=1)

24. 25.538 43.196 ↓ 1.0 103,174 1

Hash (cost=3,416.56..3,416.56 rows=102,956 width=33) (actual time=43.195..43.196 rows=103,174 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 7,574kB
25. 17.658 17.658 ↓ 1.0 103,174 1

Seq Scan on event_orders eo (cost=0.00..3,416.56 rows=102,956 width=33) (actual time=0.012..17.658 rows=103,174 loops=1)

Planning time : 2.080 ms
Execution time : 9,119.714 ms