explain.depesz.com

PostgreSQL's explain analyze made readable

Result: G1xH

Settings
# exclusive inclusive rows x rows loops node
1. 1,195.294 1,195.294 ↑ 38.0 3 1

CTE Scan on last_completed_activities (cost=2,019,638.86..2,019,820.24 rows=114 width=36) (actual time=825.634..1,195.294 rows=3 loops=1)

  • Filter: (patient_id = ANY ('{20,21,8940}'::integer[]))
  • Rows Removed by Filter: 7653
2.          

CTE last_completed_activities

3. 143.456 1,191.769 ↓ 1.0 7,656 1

Hash Left Join (cost=40,239.15..2,019,638.86 rows=7,571 width=29) (actual time=365.027..1,191.769 rows=7,656 loops=1)

  • Hash Cond: ((SubPlan 1) = user_activities.id)
4. 10.472 10.472 ↓ 1.0 7,656 1

Seq Scan on users (cost=0.00..795.89 rows=7,571 width=21) (actual time=0.104..10.472 rows=7,656 loops=1)

  • Filter: ((deleted_at IS NULL) AND ((type)::text = 'Patient'::text))
  • Rows Removed by Filter: 5935
5. 204.443 364.055 ↑ 1.0 963,162 1

Hash (cost=23,496.62..23,496.62 rows=963,162 width=12) (actual time=364.055..364.055 rows=963,162 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 3389kB
6. 159.612 159.612 ↑ 1.0 963,162 1

Seq Scan on user_activities (cost=0.00..23,496.62 rows=963,162 width=12) (actual time=0.013..159.612 rows=963,162 loops=1)

7.          

SubPlan (forHash Left Join)

8. 11.617 673.786 ↑ 1.0 1 11,617

Limit (cost=521.38..521.39 rows=1 width=12) (actual time=0.058..0.058 rows=1 loops=11,617)

9. 162.638 662.169 ↑ 229.0 1 11,617

Sort (cost=521.38..521.96 rows=229 width=12) (actual time=0.057..0.057 rows=1 loops=11,617)

  • Sort Key: user_activities_1.completed_on DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 25kB
10. 499.531 499.531 ↑ 2.0 116 11,617

Index Scan using index_user_activities_on_user_id on user_activities user_activities_1 (cost=0.42..520.24 rows=229 width=12) (actual time=0.004..0.043 rows=116 loops=11,617)

  • Index Cond: (user_id = users.id)