explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ThPI

Settings
# exclusive inclusive rows x rows loops node
1. 0.019 120,921.632 ↓ 3.0 6 1

Sort (cost=9,045.77..9,045.78 rows=2 width=1,452) (actual time=120,921.632..120,921.632 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.119 120,921.613 ↓ 3.0 6 1

Hash Full Join (cost=3,787.89..9,045.76 rows=2 width=1,452) (actual time=120,921.535..120,921.613 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 120,515.560 ↓ 3.0 6 1

Hash Full Join (cost=1,827.93..7,084.73 rows=2 width=80) (actual time=120,515.552..120,515.560 rows=6 loops=1)

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

MixedAggregate (cost=0.85..5,257.61 rows=2 width=72) (actual time=120,472.569..120,472.571 rows=6 loops=1)

  • Hash Key: COALESCE((site.name)::text, '-'::text)
  • Group Key: ()
5. 40.966 120,464.644 ↓ 2,595.0 2,595 1

Nested Loop Left Join (cost=0.85..5,257.58 rows=1 width=48) (actual time=32,316.437..120,464.644 rows=2,595 loops=1)

  • Join Filter: (site.id = _hyper_2_51_chunk.site_id)
  • Rows Removed by Join Filter: 289736
6. 1,876.237 120,392.538 ↓ 2,595.0 2,595 1

Nested Loop (cost=0.85..5,247.88 rows=1 width=20) (actual time=32,316.405..120,392.538 rows=2,595 loops=1)

  • Join Filter: (_hyper_3_52_chunk.session_id = _hyper_2_51_chunk.id)
  • Rows Removed by Join Filter: 37043519
7. 1.696 25.453 ↓ 2,954.0 2,954 1

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

8. 23.757 23.757 ↓ 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.027..23.757 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,245.040 118,490.848 ↓ 4.7 12,541 2,954

Append (cost=0.43..2,577.89 rows=2,685 width=32) (actual time=0.013..40.112 rows=12,541 loops=2,954)

10. 116,245.808 116,245.808 ↓ 4.7 12,541 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,564.47 rows=2,685 width=32) (actual time=0.012..39.352 rows=12,541 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))
  • Filter: (location_href !~~* '%lang%'::text)
  • Rows Removed by Filter: 698
11. 31.140 31.140 ↑ 1.8 113 2,595

Seq Scan on site (cost=0.00..7.09 rows=209 width=14) (actual time=0.003..0.012 rows=113 loops=2,595)

12. 0.007 42.972 ↓ 3.0 6 1

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

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

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

14. 0.449 42.963 ↓ 3.0 6 1

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

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

Nested Loop Left Join (cost=0.42..1,827.00 rows=1 width=48) (actual time=0.039..42.514 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.158 8.487 ↓ 2,161.0 2,161 1

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

17. 8.329 8.329 ↓ 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.022..8.329 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. 17.288 17.288 ↑ 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.008 rows=112 loops=2,161)

19. 0.007 405.934 ↓ 3.0 6 1

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

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

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

21. 5.529 405.923 ↓ 3.0 6 1

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

  • Hash Key: COALESCE((site_2.name)::text, '-'::text)
  • Group Key: ()
22. 60.565 400.394 ↓ 7,271.0 7,271 1

Nested Loop Left Join (cost=1.28..1,959.54 rows=1 width=52) (actual time=0.139..400.394 rows=7,271 loops=1)

  • Join Filter: (site_2.id = _hyper_2_51_chunk_2.site_id)
  • Rows Removed by Join Filter: 812971
23. 2.042 274.390 ↓ 7,271.0 7,271 1

Nested Loop (cost=1.28..1,949.84 rows=1 width=24) (actual time=0.113..274.390 rows=7,271 loops=1)

24. 13.879 172.182 ↓ 9,106.0 9,106 1

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

25. 2.400 33.931 ↓ 55.5 31,093 1

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

26. 31.531 31.531 ↓ 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.025..31.531 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 124.372 ↓ 0.0 0 31,093

Append (cost=0.42..2.43 rows=1 width=20) (actual time=0.004..0.004 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. 9.106 100.166 ↑ 1.0 1 9,106

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

30. 91.060 91.060 ↑ 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.010..0.010 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: ((location_href !~~* '%lang%'::text) AND (property_id = 95))
  • Rows Removed by Filter: 0
31. 65.439 65.439 ↑ 1.8 113 7,271

Seq Scan on site site_2 (cost=0.00..7.09 rows=209 width=14) (actual time=0.001..0.009 rows=113 loops=7,271)

Planning time : 3.663 ms
Execution time : 120,922.003 ms