explain.depesz.com

PostgreSQL's explain analyze made readable

Result: R5qG

Settings
# exclusive inclusive rows x rows loops node
1. 106.161 637.240 ↓ 2.0 299,652 1

Unique (cost=37,093.57..39,741.74 rows=151,324 width=148) (actual time=474.281..637.240 rows=299,652 loops=1)

2.          

CTE previous_users

3. 0.000 0.005 ↓ 0.0 0 1

Unique (cost=9.51..9.52 rows=1 width=160) (actual time=0.005..0.005 rows=0 loops=1)

4. 0.002 0.005 ↓ 0.0 0 1

Sort (cost=9.51..9.52 rows=2 width=160) (actual time=0.005..0.005 rows=0 loops=1)

  • Sort Key: delta.user_id, delta.id
  • Sort Method: quicksort Memory: 25kB
5. 0.002 0.003 ↓ 0.0 0 1

Bitmap Heap Scan on big_query_delta delta (cost=4.16..9.50 rows=2 width=160) (actual time=0.003..0.003 rows=0 loops=1)

  • Recheck Cond: (source_table_id = 1)
6. 0.001 0.001 ↓ 0.0 0 1

Bitmap Index Scan on big_query_idx1 (cost=0.00..4.16 rows=2 width=0) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (source_table_id = 1)
7. 348.759 531.079 ↓ 2.0 299,652 1

Sort (cost=37,084.04..37,462.35 rows=151,324 width=148) (actual time=474.280..531.079 rows=299,652 loops=1)

  • Sort Key: cur.user_id, (1), cur.action_at, (1), cur.source_table_pk, cur.hash
  • Sort Method: external sort Disk: 19,632kB
8. 32.092 182.320 ↓ 2.0 299,652 1

Append (cost=32.03..12,682.67 rows=151,324 width=148) (actual time=0.029..182.320 rows=299,652 loops=1)

9. 0.001 0.007 ↓ 0.0 0 1

Nested Loop (cost=32.03..2,629.48 rows=1,498 width=148) (actual time=0.007..0.007 rows=0 loops=1)

10. 0.006 0.006 ↓ 0.0 0 1

CTE Scan on previous_users prev (cost=0.00..0.02 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=1)

  • Filter: (action_id = 3)
11. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on _big_query_delta_current_users cur (cost=32.03..2,614.47 rows=1,498 width=148) (never executed)

  • Recheck Cond: (user_id = prev.user_id)
12. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on _big_query_delta_current_users_idx1 (cost=0.00..31.66 rows=1,498 width=0) (never executed)

  • Index Cond: (user_id = prev.user_id)
13. 89.517 150.221 ↓ 2.0 299,652 1

Hash Anti Join (cost=0.03..8,539.95 rows=149,826 width=148) (actual time=0.021..150.221 rows=299,652 loops=1)

  • Hash Cond: (cur_1.user_id = prev_1.user_id)
14. 60.704 60.704 ↑ 1.0 299,652 1

Seq Scan on _big_query_delta_current_users cur_1 (cost=0.00..6,086.52 rows=299,652 width=148) (actual time=0.015..60.704 rows=299,652 loops=1)

15. 0.000 0.000 ↓ 0.0 0 1

Hash (cost=0.02..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 0kB
16. 0.000 0.000 ↓ 0.0 0 1

CTE Scan on previous_users prev_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=1)

Planning time : 0.208 ms
Execution time : 660.653 ms