explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Wulkr

Settings
# exclusive inclusive rows x rows loops node
1. 11.409 20,533.807 ↓ 0.0 0 1

Update on ig_account_aggregate_stat_histories (cost=1,754,207.89..1,867,184.34 rows=1,126 width=122) (actual time=20,533.799..20,533.807 rows=0 loops=1)

2.          

CTE mid_months

3. 0.024 0.030 ↑ 1.0 1 1

Aggregate (cost=52.52..52.53 rows=1 width=64) (actual time=0.029..0.030 rows=1 loops=1)

4. 0.005 0.006 ↑ 83.3 12 1

ProjectSet (cost=0.00..5.02 rows=1,000 width=4) (actual time=0.003..0.006 rows=12 loops=1)

5. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)

6. 0.138 20,522.398 ↑ 9.5 119 1

Merge Join (cost=1,754,155.36..1,867,131.81 rows=1,126 width=122) (actual time=20,522.141..20,522.398 rows=119 loops=1)

  • Merge Cond: ((ig_account_aggregate_stat_histories.account_id = follower_counts.ig_account_id) AND (((ig_account_aggregate_stat_histories.year)::double precision) = follower_counts.year) AND (((ig_account_aggregate_stat_histories.month)::double precision) = follower_counts.month))
  • Join Filter: ((ig_account_aggregate_stat_histories.follower_count)::double precision IS DISTINCT FROM follower_counts.interpolated)
  • Rows Removed by Join Filter: 61
7. 8,135.613 12,814.817 ↑ 33,024.2 196 1

Sort (cost=1,346,457.22..1,362,639.08 rows=6,472,744 width=70) (actual time=12,814.750..12,814.817 rows=196 loops=1)

  • Sort Key: ig_account_aggregate_stat_histories.account_id, ((ig_account_aggregate_stat_histories.year)::double precision), ((ig_account_aggregate_stat_histories.month)::double precision)
  • Sort Method: external merge Disk: 619,624kB
8. 4,679.204 4,679.204 ↑ 1.0 6,423,618 1

Seq Scan on ig_account_aggregate_stat_histories (cost=0.00..310,785.44 rows=6,472,744 width=70) (actual time=0.571..4,679.204 rows=6,423,618 loops=1)

9. 0.045 7,707.443 ↑ 4,197.2 216 1

Materialize (cost=407,698.13..412,231.13 rows=906,600 width=80) (actual time=7,707.378..7,707.443 rows=216 loops=1)

10. 0.122 7,707.398 ↑ 4,197.2 216 1

Sort (cost=407,698.13..409,964.63 rows=906,600 width=80) (actual time=7,707.376..7,707.398 rows=216 loops=1)

  • Sort Key: follower_counts.ig_account_id, follower_counts.year, follower_counts.month
  • Sort Method: quicksort Memory: 55kB
11. 0.106 7,707.276 ↑ 4,197.2 216 1

Subquery Scan on follower_counts (cost=0.63..294,969.58 rows=906,600 width=80) (actual time=687.374..7,707.276 rows=216 loops=1)

12. 0.120 7,707.170 ↑ 4,197.2 216 1

Result (cost=0.63..285,903.58 rows=906,600 width=28) (actual time=687.361..7,707.170 rows=216 loops=1)

13.          

Initplan (for Result)

14. 0.032 0.032 ↑ 1.0 1 1

CTE Scan on mid_months (cost=0.00..0.02 rows=1 width=32) (actual time=0.031..0.032 rows=1 loops=1)

15. 0.003 0.003 ↑ 1.0 1 1

CTE Scan on mid_months mid_months_1 (cost=0.00..0.02 rows=1 width=32) (actual time=0.002..0.003 rows=1 loops=1)

16. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on mid_months mid_months_2 (cost=0.00..0.02 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)

17. 118.850 7,707.014 ↑ 4,197.2 216 1

ProjectSet (cost=0.57..16,190.02 rows=906,600 width=28) (actual time=687.356..7,707.014 rows=216 loops=1)

18. 23.111 7,588.164 ↑ 503.7 18 1

GroupAggregate (cost=0.57..8,959.89 rows=9,066 width=68) (actual time=583.380..7,588.164 rows=18 loops=1)

  • Group Key: ig_follower_counts.ig_account_id
19. 7,565.053 7,565.053 ↓ 1.4 12,872 1

Index Scan using index_ig_follower_counts_on_ig_account_id_and_created_at on ig_follower_counts (cost=0.57..8,732.06 rows=9,184 width=16) (actual time=1.175..7,565.053 rows=12,872 loops=1)

  • Index Cond: ((ig_account_id = ANY ('{1,2,3,4,5,6,7,8,11,12,13,14,15,16,17,18,19,20,21,22}'::integer[])) AND (created_at >= '2019-10-11 23:55:52.310005'::timestamp without time zone))
Planning time : 0.757 ms
Execution time : 20,646.312 ms