explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PRtl

Settings
# exclusive inclusive rows x rows loops node
1. 1,282.749 1,737.668 ↑ 56.3 202,972 1

Hash Left Join (cost=649.41..4,203,860.51 rows=11,433,700 width=784) (actual time=1.740..1,737.668 rows=202,972 loops=1)

  • Hash Cond: (t.marketer_id = m2.marketer_id)
2. 134.277 454.441 ↓ 7.6 202,972 1

Hash Left Join (cost=311.33..86,493.02 rows=26,590 width=777) (actual time=1.093..454.441 rows=202,972 loops=1)

  • Hash Cond: ((t.marketer_id = m.marketer_id) AND (((t.dimension_json ->> 'campaign_id'::text))::integer = m.campaign_id))
3. 22.633 319.470 ↓ 7.6 202,972 1

Nested Loop (cost=0.00..86,032.09 rows=26,590 width=773) (actual time=0.319..319.470 rows=202,972 loops=1)

4. 0.023 0.023 ↑ 1.0 2 1

Seq Scan on dim_media_performance_time_types tt (cost=0.00..1.25 rows=2 width=65) (actual time=0.020..0.023 rows=2 loops=1)

  • Filter: ('CTD'::text = ANY (time_type_codes))
  • Rows Removed by Filter: 4
5. 14.958 296.814 ↓ 2.7 101,486 2

Append (cost=0.00..41,899.00 rows=37,214 width=726) (actual time=0.169..148.407 rows=101,486 loops=2)

6. 0.004 0.004 ↓ 0.0 0 2

Seq Scan on agg_media_performance t (cost=0.00..0.00 rows=1 width=172) (actual time=0.002..0.002 rows=0 loops=2)

  • Filter: (((dimension_json ->> 'campaign_id'::text) IS NOT NULL) AND (marketer_id = 61732) AND (time_period_start_ts >= tt.time_period_start_ts) AND (time_period_start_ts < tt.time_period_end_ts) AND ((tt.time_type_code)::text = (time_type_code)::text))
7. 0.058 0.058 ↓ 0.0 0 2

Index Scan using agg_media_performance_daily_dmm_search_idx on agg_media_performance_daily_dmm t_1 (cost=0.43..8,785.09 rows=7,656 width=718) (actual time=0.029..0.029 rows=0 loops=2)

  • Index Cond: ((marketer_id = 61732) AND ((time_type_code)::text = (tt.time_type_code)::text) AND (time_period_start_ts >= tt.time_period_start_ts) AND (time_period_start_ts < tt.time_period_end_ts))
  • Filter: ((dimension_json ->> 'campaign_id'::text) IS NOT NULL)
8. 0.038 0.038 ↓ 0.0 0 2

Index Scan using agg_media_performance_daily_conv_search_idx on agg_media_performance_daily_conv t_2 (cost=0.43..194.09 rows=167 width=925) (actual time=0.019..0.019 rows=0 loops=2)

  • Index Cond: ((marketer_id = 61732) AND ((time_type_code)::text = (tt.time_type_code)::text) AND (time_period_start_ts >= tt.time_period_start_ts) AND (time_period_start_ts < tt.time_period_end_ts))
  • Filter: ((dimension_json ->> 'campaign_id'::text) IS NOT NULL)
9. 0.030 0.030 ↓ 0.0 0 2

Index Scan using agg_media_performance_weekly_search_idx on agg_media_performance_weekly t_3 (cost=0.42..17.58 rows=14 width=521) (actual time=0.015..0.015 rows=0 loops=2)

  • Index Cond: ((marketer_id = 61732) AND ((time_type_code)::text = (tt.time_type_code)::text) AND (time_period_start_ts >= tt.time_period_start_ts) AND (time_period_start_ts < tt.time_period_end_ts))
  • Filter: ((dimension_json ->> 'campaign_id'::text) IS NOT NULL)
10. 278.852 278.852 ↓ 4.5 99,902 2

Index Scan using agg_media_performance_ctd_search_idx on agg_media_performance_ctd t_4 (cost=0.56..23,892.42 rows=22,072 width=672) (actual time=0.025..139.426 rows=99,902 loops=2)

  • Index Cond: ((marketer_id = 61732) AND ((time_type_code)::text = (tt.time_type_code)::text) AND (time_period_start_ts >= tt.time_period_start_ts) AND (time_period_start_ts < tt.time_period_end_ts))
  • Filter: ((dimension_json ->> 'campaign_id'::text) IS NOT NULL)
11. 0.060 0.060 ↓ 4.8 19 2

Index Scan using agg_media_performance_ctd_ps_search_idx on agg_media_performance_ctd_ps t_5 (cost=0.41..13.77 rows=4 width=282) (actual time=0.019..0.030 rows=19 loops=2)

  • Index Cond: ((marketer_id = 61732) AND ((time_type_code)::text = (tt.time_type_code)::text) AND (time_period_start_ts >= tt.time_period_start_ts) AND (time_period_start_ts < tt.time_period_end_ts))
  • Filter: ((dimension_json ->> 'campaign_id'::text) IS NOT NULL)
12. 2.790 2.790 ↑ 4.7 1,564 2

Index Scan using agg_media_performance_hourly_dmm_search_idx on agg_media_performance_hourly_dmm t_6 (cost=0.56..8,425.30 rows=7,290 width=898) (actual time=0.060..1.395 rows=1,564 loops=2)

  • Index Cond: ((marketer_id = 61732) AND ((time_type_code)::text = (tt.time_type_code)::text) AND (time_period_start_ts >= tt.time_period_start_ts) AND (time_period_start_ts < tt.time_period_end_ts))
  • Filter: ((dimension_json ->> 'campaign_id'::text) IS NOT NULL)
13. 0.024 0.024 ↓ 0.0 0 2

Index Scan using agg_media_performance_hourly_conv_search_idx on agg_media_performance_hourly_conv t_7 (cost=0.29..12.53 rows=10 width=711) (actual time=0.012..0.012 rows=0 loops=2)

  • Index Cond: ((marketer_id = 61732) AND ((time_type_code)::text = (tt.time_type_code)::text) AND (time_period_start_ts >= tt.time_period_start_ts) AND (time_period_start_ts < tt.time_period_end_ts))
  • Filter: ((dimension_json ->> 'campaign_id'::text) IS NOT NULL)
14. 0.222 0.694 ↑ 1.0 470 1

Hash (cost=294.88..294.88 rows=470 width=12) (actual time=0.694..0.694 rows=470 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
15. 0.472 0.472 ↑ 1.0 470 1

Index Scan using idx_dim_media on dim_media m (cost=0.29..294.88 rows=470 width=12) (actual time=0.023..0.472 rows=470 loops=1)

  • Index Cond: (marketer_id = 61732)
16. 0.006 0.478 ↑ 430.0 1 1

Hash (cost=324.10..324.10 rows=430 width=20) (actual time=0.478..0.478 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.002 0.472 ↑ 430.0 1 1

Subquery Scan on m2 (cost=0.29..324.10 rows=430 width=20) (actual time=0.471..0.472 rows=1 loops=1)

18. 0.225 0.470 ↑ 430.0 1 1

GroupAggregate (cost=0.29..311.20 rows=430 width=20) (actual time=0.470..0.470 rows=1 loops=1)

  • Group Key: dim_media.marketer_id
19. 0.245 0.245 ↑ 1.0 470 1

Index Scan using idx_dim_media on dim_media (cost=0.29..294.88 rows=470 width=8) (actual time=0.014..0.245 rows=470 loops=1)

  • Index Cond: (marketer_id = 61732)
Planning time : 3.985 ms
Execution time : 1,746.364 ms