explain.depesz.com

PostgreSQL's explain analyze made readable

Result: em1H

Settings
# exclusive inclusive rows x rows loops node
1. 0.020 37,115.455 ↓ 3.0 6 1

Sort (cost=9,039.33..9,039.33 rows=2 width=1,452) (actual time=37,115.454..37,115.455 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.144 37,115.435 ↓ 3.0 6 1

Hash Full Join (cost=3,787.88..9,039.32 rows=2 width=1,452) (actual time=37,115.357..37,115.435 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.018 36,574.668 ↓ 3.0 6 1

Hash Full Join (cost=1,827.93..7,078.29 rows=2 width=80) (actual time=36,574.661..36,574.668 rows=6 loops=1)

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

MixedAggregate (cost=0.85..5,251.17 rows=2 width=72) (actual time=36,500.667..36,500.669 rows=6 loops=1)

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

Nested Loop Left Join (cost=0.85..5,251.14 rows=1 width=48) (actual time=9,825.133..36,492.621 rows=3,258 loops=1)

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

Nested Loop (cost=0.85..5,241.44 rows=1 width=20) (actual time=9,825.099..36,406.682 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.362 23.989 ↓ 2,954.0 2,954 1

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

8. 22.627 22.627 ↓ 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.031..22.627 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,691.094 33,690.370 ↓ 4.9 13,239 2,954

Append (cost=0.43..2,571.23 rows=2,703 width=32) (actual time=0.012..11.405 rows=13,239 loops=2,954)

10. 30,999.276 30,999.276 ↓ 4.9 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..2,557.71 rows=2,703 width=32) (actual time=0.011..10.494 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.003..0.012 rows=112 loops=3,258)

12. 0.078 73.981 ↓ 3.0 6 1

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

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

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

14. 0.495 73.901 ↓ 3.0 6 1

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

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

Nested Loop Left Join (cost=0.42..1,827.00 rows=1 width=48) (actual time=0.043..73.406 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.165 9.231 ↓ 2,161.0 2,161 1

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

17. 9.066 9.066 ↓ 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.023..9.066 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. 41.059 41.059 ↑ 1.9 112 2,161

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

19. 0.007 540.623 ↓ 3.0 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
20. 0.004 540.616 ↓ 3.0 6 1

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

21. 6.721 540.612 ↓ 3.0 6 1

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

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

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

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

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

24. 13.021 216.436 ↓ 9,106.0 9,106 1

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

25. 2.116 47.950 ↓ 55.5 31,093 1

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

26. 45.834 45.834 ↓ 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.021..45.834 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 155.465 ↓ 0.0 0 31,093

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

28. 155.465 155.465 ↓ 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.005..0.005 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 72.848 ↑ 1.0 1 9,106

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

30. 72.848 72.848 ↑ 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.008 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. 126.490 126.490 ↑ 1.9 112 9,035

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

Planning time : 2.704 ms
Execution time : 37,115.832 ms