explain.depesz.com

PostgreSQL's explain analyze made readable

Result: THga

Settings
# exclusive inclusive rows x rows loops node
1. 17.645 229,752.574 ↑ 231.6 27,020 1

Subquery Scan on tbl (cost=13,328,739,160.47..13,328,833,012.26 rows=6,256,786 width=628) (actual time=229,728.213..229,752.574 rows=27,020 loops=1)

2. 63.249 229,734.929 ↑ 231.6 27,020 1

Sort (cost=13,328,739,160.47..13,328,754,802.44 rows=6,256,786 width=92) (actual time=229,728.203..229,734.929 rows=27,020 loops=1)

  • Sort Key: ((ig_accounts.ct_id)::bigint), (to_char(((cdate.cdate)::date)::timestamp with time zone, 'MM/dd/YYYY'::text))
  • Sort Method: quicksort Memory: 4568kB
3. 131.829 229,671.680 ↑ 231.6 27,020 1

GroupAggregate (cost=7,575,064.37..13,327,391,292.49 rows=6,256,786 width=92) (actual time=89,606.480..229,671.680 rows=27,020 loops=1)

  • Group Key: warner_artists.warner_id, (to_char(((cdate.cdate)::date)::timestamp with time zone, 'MM/dd/YYYY'::text)), ig_accounts.id
4. 63.689 89,441.151 ↑ 201.9 30,994 1

Sort (cost=7,575,064.37..7,590,706.34 rows=6,256,786 width=68) (actual time=89,422.074..89,441.151 rows=30,994 loops=1)

  • Sort Key: warner_artists.warner_id, (to_char(((cdate.cdate)::date)::timestamp with time zone, 'MM/dd/YYYY'::text)), ig_accounts.id
  • Sort Method: quicksort Memory: 3727kB
5. 14,447.487 89,377.462 ↑ 201.9 30,994 1

Hash Right Join (cost=295,190.80..6,355,513.39 rows=6,256,786 width=68) (actual time=8,841.843..89,377.462 rows=30,994 loops=1)

  • Hash Cond: ((ig_posts.ig_account_id = ig_accounts.id) AND ((ig_posts.created_at)::date = (cdate.cdate)::date))
6. 74,683.395 74,683.395 ↓ 1.1 22,396,285 1

Seq Scan on ig_posts (cost=0.00..2,836,003.44 rows=20,986,644 width=28) (actual time=1.142..74,683.395 rows=22,396,285 loops=1)

7. 53.577 246.580 ↑ 231.6 27,020 1

Hash (cost=164,678.01..164,678.01 rows=6,256,786 width=20) (actual time=246.580..246.580 rows=27,020 loops=1)

  • Buckets: 262144 Batches: 64 Memory Usage: 2069kB
8. 12.894 193.003 ↑ 231.6 27,020 1

Nested Loop (cost=0.94..164,678.01 rows=6,256,786 width=20) (actual time=0.068..193.003 rows=27,020 loops=1)

9. 0.039 0.039 ↑ 100.0 10 1

Function Scan on generate_series cdate (cost=0.02..10.02 rows=1,000 width=8) (actual time=0.034..0.039 rows=10 loops=1)

10. 6.954 180.070 ↑ 2.3 2,702 10

Materialize (cost=0.92..86,471.13 rows=6,257 width=12) (actual time=0.003..18.007 rows=2,702 loops=10)

11. 27.706 173.116 ↑ 2.3 2,702 1

Merge Join (cost=0.92..86,439.85 rows=6,257 width=12) (actual time=0.029..173.116 rows=2,702 loops=1)

  • Merge Cond: (warner_artists.artist_id = ig_accounts.artist_id)
12. 68.444 68.444 ↑ 2.0 8,356 1

Index Scan using index_warner_artists_on_artist_id on warner_artists (cost=0.29..1,403.62 rows=16,317 width=8) (actual time=0.006..68.444 rows=8,356 loops=1)

13. 76.966 76.966 ↑ 4.7 50,694 1

Index Scan using index_ig_accounts_on_artist_id on ig_accounts (cost=0.55..457,083.30 rows=238,587 width=12) (actual time=0.004..76.966 rows=50,694 loops=1)

  • Filter: (ct_id IS NOT NULL)
  • Rows Removed by Filter: 5592
14.          

SubPlan (forGroupAggregate)

15. 27.020 132,479.060 ↑ 1.0 1 27,020

Limit (cost=0.56..1,064.74 rows=1 width=4) (actual time=4.902..4.903 rows=1 loops=27,020)

16. 132,452.040 132,452.040 ↑ 1.0 1 27,020

Index Scan using index_ig_follower_counts_on_ig_account_id_and_created_at on ig_follower_counts (cost=0.56..1,064.74 rows=1 width=4) (actual time=4.902..4.902 rows=1 loops=27,020)

  • Index Cond: (ig_account_id = ig_accounts.id)
  • Filter: ((created_at)::date = (min((cdate.cdate)::date) - 1))
  • Rows Removed by Filter: 259
17. 27.020 7,619.640 ↑ 1.0 1 27,020

Limit (cost=0.56..1,064.07 rows=1 width=4) (actual time=0.282..0.282 rows=1 loops=27,020)

18. 7,592.620 7,592.620 ↑ 1.0 1 27,020

Index Scan using index_ig_follower_counts_on_ig_account_id_and_created_at on ig_follower_counts ig_follower_counts_1 (cost=0.56..1,064.07 rows=1 width=4) (actual time=0.281..0.281 rows=1 loops=27,020)

  • Index Cond: (ig_account_id = ig_accounts.id)
  • Filter: ((created_at)::date = min((cdate.cdate)::date))
  • Rows Removed by Filter: 260