explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3a1N

Settings
# exclusive inclusive rows x rows loops node
1. 0.025 34,466.593 ↓ 3.0 6 1

Sort (cost=10,031.82..10,031.82 rows=2 width=1,452) (actual time=34,466.593..34,466.593 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.134 34,466.568 ↓ 3.0 6 1

Hash Full Join (cost=3,787.88..10,031.81 rows=2 width=1,452) (actual time=34,466.490..34,466.568 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 34,055.393 ↓ 3.0 6 1

Hash Full Join (cost=1,827.93..8,070.78 rows=2 width=80) (actual time=34,055.386..34,055.393 rows=6 loops=1)

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

MixedAggregate (cost=0.85..6,243.66 rows=2 width=72) (actual time=34,003.865..34,003.867 rows=6 loops=1)

  • Hash Key: COALESCE((site.name)::text, '-'::text)
  • Group Key: ()
5. 49.520 33,995.652 ↓ 3,258.0 3,258 1

Nested Loop Left Join (cost=0.85..6,243.63 rows=1 width=48) (actual time=9,014.409..33,995.652 rows=3,258 loops=1)

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

Nested Loop (cost=0.85..6,233.93 rows=1 width=20) (actual time=9,014.376..33,910.294 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.364 23.903 ↓ 2,954.0 2,954 1

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

8. 22.539 22.539 ↓ 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.017..22.539 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,490.222 31,294.676 ↓ 3.5 13,239 2,954

Append (cost=0.43..3,550.58 rows=3,754 width=32) (actual time=0.010..10.594 rows=13,239 loops=2,954)

10. 28,804.454 28,804.454 ↓ 3.5 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,531.81 rows=3,754 width=32) (actual time=0.010..9.751 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. 35.838 35.838 ↑ 1.9 112 3,258

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

12. 0.007 51.508 ↓ 3.0 6 1

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

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

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

14. 0.540 51.499 ↓ 3.0 6 1

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

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

Nested Loop Left Join (cost=0.42..1,827.00 rows=1 width=48) (actual time=0.042..50.959 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.152 9.373 ↓ 2,161.0 2,161 1

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

17. 9.221 9.221 ↓ 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..9.221 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. 23.771 23.771 ↑ 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.011 rows=112 loops=2,161)

19. 0.008 411.041 ↓ 3.0 6 1

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

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

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

21. 7.423 411.030 ↓ 3.0 6 1

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

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

Nested Loop Left Join (cost=1.28..1,959.54 rows=1 width=52) (actual time=0.137..403.607 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.631 238.540 ↓ 9,035.0 9,035 1

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

24. 0.000 187.379 ↓ 9,106.0 9,106 1

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

25. 2.097 32.894 ↓ 55.5 31,093 1

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

26. 30.797 30.797 ↓ 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.023..30.797 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. 31.093 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. 124.372 124.372 ↓ 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.004..0.004 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 45.530 ↑ 1.0 1 9,106

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

30. 45.530 45.530 ↑ 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.005..0.005 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. 90.350 90.350 ↑ 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.010 rows=112 loops=9,035)

Planning time : 3.135 ms
Execution time : 34,466.937 ms