explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7Qtf

Settings
# exclusive inclusive rows x rows loops node
1. 0.026 37,013.824 ↓ 3.0 6 1

Sort (cost=9,835.67..9,835.68 rows=2 width=1,452) (actual time=37,013.824..37,013.824 rows=6 loops=1)

  • Sort Key: (CASE COALESCE(data_stats_session."Publisher", (COALESCE((COALESCE((site.name)::text, '-'::text)), '[TOTAL]'::text)), data_stats_event."Publisher") WHEN '[TOTAL]'::text THEN 0 ELSE 1 END), (COALESCE(data_stats_session."Publisher", (COALESCE((COALESCE((site.name)::text, '-'::text)), '[TOTAL]'::text)), data_stats_event."Publisher"))
  • Sort Method: quicksort Memory: 31kB
2. 0.148 37,013.798 ↓ 3.0 6 1

Hash Full Join (cost=3,787.88..9,835.66 rows=2 width=1,452) (actual time=37,013.697..37,013.798 rows=6 loops=1)

  • Hash Cond: (COALESCE(data_stats_session."Publisher", (COALESCE((COALESCE((site.name)::text, '-'::text)), '[TOTAL]'::text))) = data_stats_event."Publisher")
3. 0.017 36,350.360 ↓ 3.0 6 1

Hash Full Join (cost=1,827.93..7,874.64 rows=2 width=80) (actual time=36,350.353..36,350.360 rows=6 loops=1)

  • Hash Cond: ((COALESCE((COALESCE((site.name)::text, '-'::text)), '[TOTAL]'::text)) = COALESCE(data_stats_session."Publisher"))
4. 7.758 36,265.731 ↓ 3.0 6 1

MixedAggregate (cost=0.85..6,047.52 rows=2 width=72) (actual time=36,265.729..36,265.731 rows=6 loops=1)

  • Hash Key: COALESCE((site.name)::text, '-'::text)
  • Group Key: ()
5. 45.887 36,257.973 ↓ 3,258.0 3,258 1

Nested Loop Left Join (cost=0.85..6,047.49 rows=1 width=48) (actual time=9,775.501..36,257.973 rows=3,258 loops=1)

  • Join Filter: (site.id = _hyper_2_51_chunk.site_id)
  • Rows Removed by Join Filter: 362067
6. 2,633.139 36,172.990 ↓ 3,258.0 3,258 1

Nested Loop (cost=0.85..6,037.79 rows=1 width=20) (actual time=9,775.468..36,172.990 rows=3,258 loops=1)

  • Join Filter: (_hyper_3_52_chunk.session_id = _hyper_2_51_chunk.id)
  • Rows Removed by Join Filter: 39104748
7. 1.366 23.767 ↓ 2,954.0 2,954 1

Append (cost=0.42..2,636.43 rows=1 width=20) (actual time=0.018..23.767 rows=2,954 loops=1)

8. 22.401 22.401 ↓ 2,954.0 2,954 1

Index Scan using _hyper_2_51_chunk_tmp_session_property_id_and_created_at on _hyper_2_51_chunk (cost=0.42..2,636.42 rows=1 width=20) (actual time=0.018..22.401 rows=2,954 loops=1)

  • Index Cond: ((property_id = 95) AND (created_at >= '2019-11-02 23:00:00+00'::timestamp with time zone) AND (created_at <= '2019-11-10 22:59:59+00'::timestamp with time zone))
  • Filter: ((campaign_id = 79) AND (((geo_country_code)::text = 'CH'::text) OR ((geo_country_code)::text = 'IT'::text) OR ((geo_country_code)::text = 'DE'::text) OR ((geo_country_code)::text = 'FR'::text)) AND ((site_id = 168) OR (site_id = 169) OR (site_id = 170) OR (site_id = 171) OR (site_id = 172) OR (site_id = 173) OR (site_id = 167) OR (site_id = 226) OR (site_id = 227)))
  • Rows Removed by Filter: 9331
9. 2,445.912 33,516.084 ↓ 3.7 13,239 2,954

Append (cost=0.43..3,357.22 rows=3,531 width=32) (actual time=0.011..11.346 rows=13,239 loops=2,954)

10. 31,070.172 31,070.172 ↓ 3.7 13,239 2,954

Index Scan using _hyper_3_52_chunk_tmp_pageview_property_id_and_created_at on _hyper_3_52_chunk (cost=0.43..3,339.57 rows=3,531 width=32) (actual time=0.011..10.518 rows=13,239 loops=2,954)

  • Index Cond: ((property_id = 95) AND (created_at >= '2019-11-03 23:00:00+00'::timestamp with time zone) AND (created_at <= '2019-11-10 22:59:59+00'::timestamp with time zone))
11. 39.096 39.096 ↑ 1.9 112 3,258

Seq Scan on site (cost=0.00..7.09 rows=209 width=14) (actual time=0.002..0.012 rows=112 loops=3,258)

12. 0.007 84.612 ↓ 3.0 6 1

Hash (cost=1,827.05..1,827.05 rows=2 width=40) (actual time=84.612..84.612 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.002 84.605 ↓ 3.0 6 1

Subquery Scan on data_stats_session (cost=0.42..1,827.05 rows=2 width=40) (actual time=84.602..84.605 rows=6 loops=1)

14. 0.506 84.603 ↓ 3.0 6 1

MixedAggregate (cost=0.42..1,827.03 rows=2 width=72) (actual time=84.601..84.603 rows=6 loops=1)

  • Hash Key: COALESCE((site_1.name)::text, '-'::text)
  • Group Key: ()
15. 34.345 84.097 ↓ 2,161.0 2,161 1

Nested Loop Left Join (cost=0.42..1,827.00 rows=1 width=48) (actual time=0.042..84.097 rows=2,161 loops=1)

  • Join Filter: (site_1.id = _hyper_2_51_chunk_1.site_id)
  • Rows Removed by Join Filter: 239471
16. 0.176 15.176 ↓ 2,161.0 2,161 1

Append (cost=0.42..1,817.30 rows=1 width=20) (actual time=0.025..15.176 rows=2,161 loops=1)

17. 15.000 15.000 ↓ 2,161.0 2,161 1

Index Scan using _hyper_2_51_chunk_tmp_session_property_id_and_created_at on _hyper_2_51_chunk _hyper_2_51_chunk_1 (cost=0.42..1,817.29 rows=1 width=20) (actual time=0.025..15.000 rows=2,161 loops=1)

  • Index Cond: ((property_id = 95) AND (created_at >= '2019-11-03 23:00:00+00'::timestamp with time zone) AND (created_at <= '2019-11-10 22:59:59+00'::timestamp with time zone))
  • Filter: ((campaign_id = 79) AND (((geo_country_code)::text = 'CH'::text) OR ((geo_country_code)::text = 'IT'::text) OR ((geo_country_code)::text = 'DE'::text) OR ((geo_country_code)::text = 'FR'::text)) AND ((site_id = 168) OR (site_id = 169) OR (site_id = 170) OR (site_id = 171) OR (site_id = 172) OR (site_id = 173) OR (site_id = 167) OR (site_id = 226) OR (site_id = 227)))
  • Rows Removed by Filter: 6426
18. 34.576 34.576 ↑ 1.9 112 2,161

Seq Scan on site site_1 (cost=0.00..7.09 rows=209 width=14) (actual time=0.002..0.016 rows=112 loops=2,161)

19. 0.007 663.290 ↓ 3.0 6 1

Hash (cost=1,959.93..1,959.93 rows=2 width=312) (actual time=663.290..663.290 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
20. 0.003 663.283 ↓ 3.0 6 1

Subquery Scan on data_stats_event (cost=1.28..1,959.93 rows=2 width=312) (actual time=663.277..663.283 rows=6 loops=1)

21. 11.062 663.280 ↓ 3.0 6 1

MixedAggregate (cost=1.28..1,959.91 rows=2 width=344) (actual time=663.275..663.280 rows=6 loops=1)

  • Hash Key: COALESCE((site_2.name)::text, '-'::text)
  • Group Key: ()
22. 115.833 652.218 ↓ 9,035.0 9,035 1

Nested Loop Left Join (cost=1.28..1,959.54 rows=1 width=52) (actual time=0.138..652.218 rows=9,035 loops=1)

  • Join Filter: (site_2.id = _hyper_2_51_chunk_2.site_id)
  • Rows Removed by Join Filter: 1005435
23. 5.809 373.755 ↓ 9,035.0 9,035 1

Nested Loop (cost=1.28..1,949.83 rows=1 width=24) (actual time=0.110..373.755 rows=9,035 loops=1)

24. 13.093 285.992 ↓ 9,106.0 9,106 1

Nested Loop (cost=0.85..1,947.38 rows=1 width=40) (actual time=0.099..285.992 rows=9,106 loops=1)

25. 3.053 55.248 ↓ 55.5 31,093 1

Append (cost=0.43..581.38 rows=560 width=52) (actual time=0.025..55.248 rows=31,093 loops=1)

26. 52.195 52.195 ↓ 55.5 31,093 1

Index Scan using _hyper_5_53_chunk_tmp_event_event_id on _hyper_5_53_chunk (cost=0.43..578.58 rows=560 width=52) (actual time=0.024..52.195 rows=31,093 loops=1)

  • Index Cond: ((event_id = ANY ('{4285,4286,4287,4293,4294,4295,4296,4297,4298,4300,4301,4311,4312,4313,4314,4315,4324,4325,4326,4327,4328,4329,4330,4343,4344,4345,4346,4347,4348,4349,4350,4351,4352,4353,4354}'::integer[])) AND (property_id = 95) AND (created_at >= '2019-11-03 23:00:00+00'::timestamp with time zone) AND (created_at <= '2019-11-10 22:59:59+00'::timestamp with time zone))
27. 0.000 217.651 ↓ 0.0 0 31,093

Append (cost=0.42..2.43 rows=1 width=20) (actual time=0.007..0.007 rows=0 loops=31,093)

28. 217.651 217.651 ↓ 0.0 0 31,093

Index Scan using "51_69_stats_session_pkey" on _hyper_2_51_chunk _hyper_2_51_chunk_2 (cost=0.42..2.42 rows=1 width=20) (actual time=0.007..0.007 rows=0 loops=31,093)

  • Index Cond: ((id = _hyper_5_53_chunk.session_id) AND (created_at >= '2019-11-02 23:00:00+00'::timestamp with time zone) AND (created_at <= '2019-11-10 22:59:59+00'::timestamp with time zone))
  • Filter: ((property_id = 95) AND (campaign_id = 79) AND (((geo_country_code)::text = 'CH'::text) OR ((geo_country_code)::text = 'IT'::text) OR ((geo_country_code)::text = 'DE'::text) OR ((geo_country_code)::text = 'FR'::text)) AND ((site_id = 168) OR (site_id = 169) OR (site_id = 170) OR (site_id = 171) OR (site_id = 172) OR (site_id = 173) OR (site_id = 167) OR (site_id = 226) OR (site_id = 227)))
  • Rows Removed by Filter: 1
29. 0.000 81.954 ↑ 1.0 1 9,106

Append (cost=0.43..2.44 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=9,106)

30. 81.954 81.954 ↑ 1.0 1 9,106

Index Scan using "52_71_stats_page_view_pkey" on _hyper_3_52_chunk _hyper_3_52_chunk_1 (cost=0.43..2.44 rows=1 width=16) (actual time=0.008..0.009 rows=1 loops=9,106)

  • Index Cond: ((id = _hyper_5_53_chunk.page_view_id) AND (created_at >= '2019-11-02 23:00:00+00'::timestamp with time zone) AND (created_at <= '2019-11-10 22:59:59+00'::timestamp with time zone))
  • Filter: (property_id = 95)
31. 162.630 162.630 ↑ 1.9 112 9,035

Seq Scan on site site_2 (cost=0.00..7.09 rows=209 width=14) (actual time=0.002..0.018 rows=112 loops=9,035)

Planning time : 3.043 ms
Execution time : 37,014.386 ms