explain.depesz.com

PostgreSQL's explain analyze made readable

Result: K7ks

Settings
# exclusive inclusive rows x rows loops node
1. 2.835 13,771.008 ↓ 0.0 27,418 1

Limit (cost=0..0 rows=0 width=0) (actual time=13,765.795..13,771.008 rows=27,418 loops=1)

2. 9.948 13,768.173 ↓ 0.0 27,418 1

Sort (cost=0..0 rows=0 width=0) (actual time=13,765.793..13,768.173 rows=27,418 loops=1)

  • Sort Key: COALESCE((pg_catalog.sum((COALESCE((pg_catalog.sum(remote_scan.count))::bigint, '0'::bigint))))::bigint, '0'::bigint) DESC
  • Sort Method: quicksort Memory: 14,512kB
3. 36.703 13,758.225 ↓ 0.0 27,418 1

HashAggregate (cost=0..0 rows=0 width=0) (actual time=13,744.378..13,758.225 rows=27,418 loops=1)

  • Group Key: remote_scan.worker_column_4, remote_scan.extra
4. 10,345.637 13,721.522 ↓ 0.0 36,952 1

Custom Scan (cost=0..0 rows=0 width=0) (actual time=13,719..13,721.522 rows=36,952 loops=1)

5. 0.305 3,375.885 ↓ 168.0 336 1

GroupAggregate (cost=10,000,000,221.4..10,000,000,221.44 rows=2 width=73) (actual time=3,375.565..3,375.885 rows=336 loops=1)

  • Group Key: paths_subquery.path, paths_subquery.extra
  • Buffers: shared hit=261,643
6. 0.782 3,375.580 ↓ 250.0 500 1

Sort (cost=10,000,000,221.4..10,000,000,221.4 rows=2 width=33) (actual time=3,375.548..3,375.58 rows=500 loops=1)

  • Sort Key: paths_subquery.path, paths_subquery.extra
  • Sort Method: quicksort Memory: 137kB
  • Buffers: shared hit=261,643
7. 0.063 3,374.798 ↓ 250.0 500 1

Subquery Scan on paths_subquery (cost=10,000,000,221.31..10,000,000,221.39 rows=2 width=33) (actual time=3,371.749..3,374.798 rows=500 loops=1)

  • Buffers: shared hit=261,643
8. 2.782 3,374.735 ↓ 250.0 500 1

GroupAggregate (cost=10,000,000,221.31..10,000,000,221.36 rows=2 width=89) (actual time=3,371.748..3,374.735 rows=500 loops=1)

  • Group Key: events.event_id, events.user_id
  • Buffers: shared hit=261,643
9. 2.574 3,371.953 ↓ 2,044.5 4,089 1

Sort (cost=10,000,000,221.31..10,000,000,221.32 rows=2 width=64) (actual time=3,371.681..3,371.953 rows=4,089 loops=1)

  • Sort Key: events.event_id, events.user_id
  • Sort Method: quicksort Memory: 416kB
  • Buffers: shared hit=261,643
10. 2.017 3,369.379 ↓ 2,044.5 4,089 1

Nested Loop (cost=10,000,000,004.23..10,000,000,221.3 rows=2 width=64) (actual time=298.959..3,369.379 rows=4,089 loops=1)

  • Buffers: shared hit=261,643
11. 0.085 297.862 ↓ 250.0 500 1

Limit (cost=2.49..2.5 rows=2 width=64) (actual time=297.533..297.862 rows=500 loops=1)

  • Buffers: shared hit=132,606
12. 24.096 297.777 ↓ 250.0 500 1

Sort (cost=2.49..2.5 rows=2 width=64) (actual time=297.532..297.777 rows=500 loops=1)

  • Sort Key: events.event_id
  • Sort Method: top-N heapsort Memory: 139kB
  • Buffers: shared hit=132,606
13. 80.662 273.681 ↓ 71,279.5 142,559 1

Result (cost=0..2.48 rows=2 width=64) (actual time=0.035..273.681 rows=142,559 loops=1)

  • Buffers: shared hit=132,606
14. 10.909 193.019 ↓ 71,279.5 142,559 1

Append (cost=0..2.46 rows=2 width=468) (actual time=0.033..193.019 rows=142,559 loops=1)

  • Buffers: shared hit=130,943
15. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on events_4481009 events (cost=0..0 rows=1 width=64) (actual time=0.006..0.006 rows=0 loops=1)

  • Filter: ((events.user_id >= '(2841479993,0)'::app_user_id) AND (events.user_id <= '(2841479993,9007199254740991)'::app_user_id) AND (events."time" >= '1596006000000'::bigint) AND (events."time" <= '1596610800000'::bigint) AND ((events.data ->> 'path'::text) ~~* '%/etx/pw/portfolios%'::text) AND ((events.data ->> 'object'::text) = 'pageview'::text) AND ((events.data ->> 'library'::text) = 'web'::text))
16. 182.104 182.104 ↓ 142,559.0 142,559 1

Index Scan using pageviews_4481009_pim_evdef_848d29baceb3_idx on pageviews_4481009 events_1 (cost=0.43..2.45 rows=1 width=871) (actual time=0.026..182.104 rows=142,559 loops=1)

  • Index Cond: ((events_1."time" >= '1596006000000'::bigint) AND (events_1."time" <= '1596610800000'::bigint))
  • Buffers: shared hit=130,943
17. 3.000 3,069.500 ↓ 8.0 8 500

Subquery Scan on adjacent_events (cost=1.73..109.38 rows=1 width=56) (actual time=0.827..6.139 rows=8 loops=500)

  • Filter: (events.user_id = adjacent_events.user_id)
  • Buffers: shared hit=129,037
18. 1.000 3,066.500 ↓ 2.0 8 500

Limit (cost=1.73..109.33 rows=4 width=56) (actual time=0.826..6.133 rows=8 loops=500)

  • Buffers: shared hit=129,037
19. 2,829.000 3,065.500 ↓ 2.0 8 500

Subquery Scan on materialized_events (cost=1.73..109.33 rows=4 width=56) (actual time=0.825..6.131 rows=8 loops=500)

  • Filter: truncated_classify_event()
  • Buffers: shared hit=129,037
20. 193.500 236.500 ↓ 3.8 15 500

Result (cost=1.73..8.3 rows=4 width=440) (actual time=0.085..0.473 rows=15 loops=500)

  • Buffers: shared hit=113,691
21. 4.000 43.000 ↓ 3.8 15 500

Merge Append (cost=1.73..7.88 rows=4 width=648) (actual time=0.051..0.086 rows=15 loops=500)

  • Buffers: shared hit=12,747
22. 3.000 3.000 ↓ 0.0 0 500

Sort (cost=0.01..0.02 rows=1 width=56) (actual time=0.006..0.006 rows=0 loops=500)

  • Sort Key: events_2."time"
  • Sort Method: quicksort Memory: 25kB
23. 0.000 0.000 ↓ 0.0 0 500

Seq Scan on events_4481009 events_2 (cost=0..0 rows=1 width=56) (actual time=0..0 rows=0 loops=500)

  • Filter: ((events_2."time" > events."time") AND (events_2.user_id >= '(2841479993,0)'::app_user_id) AND (events_2.user_id <= '(2841479993,9007199254740991)'::app_user_id) AND (events_2.user_id = events.user_id) AND ((events_2.data ->> 'session_id'::text) = ((events.data ->> 'session_id'::text))))
24. 18.000 18.000 ↓ 10.0 10 500

Index Scan using other_events_4481009_pim_core_u_s_time_3c3f0ddaf947_idx on other_events_4481009 events_3 (cost=0.56..2.59 rows=1 width=1,184) (actual time=0.017..0.036 rows=10 loops=500)

  • Index Cond: ((events_3.user_id >= '(2841479993,0)'::app_user_id) AND (events_3.user_id <= '(2841479993,9007199254740991)'::app_user_id) AND (events_3.user_id = events.user_id) AND ((events_3.data ->> 'session_id'::text) = ((events.data ->> 'session_id'::text))) AND (events_3."time" > events."time"))
  • Buffers: shared hit=6,003
25. 13.000 13.000 ↓ 6.0 6 500

Index Scan using pageviews_4481009_pim_core_u_s_time_3c3f0ddaf947_idx on pageviews_4481009 events_4 (cost=0.56..2.59 rows=1 width=863) (actual time=0.014..0.026 rows=6 loops=500)

  • Index Cond: ((events_4.user_id >= '(2841479993,0)'::app_user_id) AND (events_4.user_id <= '(2841479993,9007199254740991)'::app_user_id) AND (events_4.user_id = events.user_id) AND ((events_4.data ->> 'session_id'::text) = ((events.data ->> 'session_id'::text))) AND (events_4."time" > events."time"))
  • Buffers: shared hit=4,742
26. 5.000 5.000 ↓ 0.0 0 500

Index Scan using sessions_4481009_pim_core_u_s_time_3c3f0ddaf947_idx on sessions_4481009 events_5 (cost=0.56..2.58 rows=1 width=489) (actual time=0.01..0.01 rows=0 loops=500)

  • Index Cond: ((events_5.user_id >= '(2841479993,0)'::app_user_id) AND (events_5.user_id <= '(2841479993,9007199254740991)'::app_user_id) AND (events_5.user_id = events.user_id) AND ((events_5.data ->> 'session_id'::text) = ((events.data ->> 'session_id'::text))) AND (events_5."time" > events."time"))
  • Buffers: shared hit=2,002
Planning time : 24.402 ms
Execution time : 13,775.868 ms