explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XUz

Settings
# exclusive inclusive rows x rows loops node
1. 0.111 1,428.003 ↑ 1.0 100 1

Limit (cost=36,467.10..37,301.66 rows=100 width=146) (actual time=1,282.218..1,428.003 rows=100 loops=1)

2. 0.468 1,427.892 ↑ 14.8 1,100 1

Subquery Scan on c1 (cost=28,121.42..164,339.57 rows=16,322 width=146) (actual time=98.660..1,427.892 rows=1,100 loops=1)

3. 2.478 1,427.424 ↑ 14.8 1,100 1

Nested Loop (cost=28,121.42..164,176.35 rows=16,322 width=206) (actual time=98.658..1,427.424 rows=1,100 loops=1)

4. 744.372 1,416.146 ↑ 14.8 1,100 1

Nested Loop Semi Join (cost=28,120.99..138,210.83 rows=16,322 width=28) (actual time=98.634..1,416.146 rows=1,100 loops=1)

  • Join Filter: (a.activity_log_id = cte.activity_log_id)
  • Rows Removed by Join Filter: 14,925,168
5. 7.154 7.154 ↑ 18.7 1,749 1

Index Scan using activity_logs_2020_2_created_idx on activity_logs_2020_2 a (cost=0.42..28,480.27 rows=32,643 width=28) (actual time=0.014..7.154 rows=1,749 loops=1)

  • Index Cond: ((created >= '2020-02-03 00:00:00'::timestamp without time zone) AND (created <= '2020-02-13 00:00:00'::timestamp without time zone))
  • Filter: (activity_log_type = 1)
  • Rows Removed by Filter: 27,168
6. 567.989 664.620 ↓ 42.7 8,534 1,749

Materialize (cost=28,120.57..28,124.07 rows=200 width=8) (actual time=0.054..0.380 rows=8,534 loops=1,749)

7. 1.036 96.631 ↓ 62.2 12,440 1

Subquery Scan on cte (cost=28,120.57..28,123.07 rows=200 width=8) (actual time=94.500..96.631 rows=12,440 loops=1)

8. 4.011 95.595 ↓ 62.2 12,440 1

Sort (cost=28,120.57..28,121.07 rows=200 width=28) (actual time=94.499..95.595 rows=12,440 loops=1)

  • Sort Key: a_1.user_id DESC
  • Sort Method: quicksort Memory: 968kB
9. 10.423 91.584 ↓ 62.2 12,440 1

HashAggregate (cost=28,110.92..28,112.92 rows=200 width=28) (actual time=89.740..91.584 rows=12,440 loops=1)

  • Group Key: a_1.user_id
10. 81.161 81.161 ↓ 1.0 32,729 1

Seq Scan on activity_logs_2020_2 a_1 (cost=0.00..27,947.71 rows=32,643 width=16) (actual time=13.635..81.161 rows=32,729 loops=1)

  • Filter: ((created >= '2020-02-03 00:00:00'::timestamp without time zone) AND (created <= '2020-02-13 00:00:00'::timestamp without time zone) AND (activity_log_type = 1))
  • Rows Removed by Filter: 667,425
11. 8.800 8.800 ↑ 1.0 1 1,100

Index Scan using pk_users on users u (cost=0.43..1.59 rows=1 width=98) (actual time=0.008..0.008 rows=1 loops=1,100)

  • Index Cond: (user_id = a.user_id)