explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eTsk

Settings
# exclusive inclusive rows x rows loops node
1. 0.038 301,928.876 ↑ 4.0 1 1

GroupAggregate (cost=760,008.73..760,008.82 rows=4 width=72) (actual time=301,928.876..301,928.876 rows=1 loops=1)

  • Group Key: res.total_count
2.          

CTE pre_filter

3. 3.938 281.096 ↓ 307.7 1,846 1

Group (cost=689,024.00..689,024.03 rows=6 width=37) (actual time=274.892..281.096 rows=1,846 loops=1)

  • Group Key: raw_tracks_2.anonymous_id
4. 5.870 277.158 ↓ 334.7 2,008 1

Sort (cost=689,024.00..689,024.01 rows=6 width=37) (actual time=274.890..277.158 rows=2,008 loops=1)

  • Sort Key: raw_tracks_2.anonymous_id
  • Sort Method: quicksort Memory: 205kB
5. 240.860 271.288 ↓ 334.7 2,008 1

Bitmap Heap Scan on raw_tracks_next raw_tracks_2 (cost=7,434.00..689,023.92 rows=6 width=37) (actual time=119.154..271.288 rows=2,008 loops=1)

  • Recheck Cond: ((pixel_key)::text = 'B_JDOUDOU'::text)
  • Filter: (((event)::text = 'sign_up'::text) AND ("timestamp" >= apiv0_private.param_to_date('01/05/2019'::text)) AND ("timestamp" <= (apiv0_private.param_to_date('05/09/2019'::text) + '30 days'::interval)))
  • Rows Removed by Filter: 157422
  • Heap Blocks: exact=138493
6. 30.428 30.428 ↑ 1.1 159,430 1

Bitmap Index Scan on pixel_key_raw_tracks_next (cost=0.00..7,434.00 rows=176,175 width=0) (actual time=30.428..30.428 rows=159,430 loops=1)

  • Index Cond: ((pixel_key)::text = 'B_JDOUDOU'::text)
7. 0.004 301,928.838 ↑ 1.0 4 1

Sort (cost=70,984.70..70,984.71 rows=4 width=40) (actual time=301,928.838..301,928.838 rows=4 loops=1)

  • Sort Key: res.total_count
  • Sort Method: quicksort Memory: 25kB
8. 0.003 301,928.834 ↑ 1.0 4 1

Subquery Scan on res (cost=70,984.52..70,984.66 rows=4 width=40) (actual time=301,928.831..301,928.834 rows=4 loops=1)

9. 0.008 301,928.831 ↑ 1.0 4 1

WindowAgg (cost=70,984.52..70,984.62 rows=4 width=40) (actual time=301,928.830..301,928.831 rows=4 loops=1)

10. 0.007 301,928.823 ↑ 1.0 4 1

Limit (cost=70,984.52..70,984.53 rows=4 width=68) (actual time=301,928.817..301,928.823 rows=4 loops=1)

11. 0.020 301,928.816 ↑ 1.5 4 1

Sort (cost=70,984.52..70,984.53 rows=6 width=68) (actual time=301,928.816..301,928.816 rows=4 loops=1)

  • Sort Key: (((res_1."row" ->> 'count'::text))::integer) DESC
  • Sort Method: top-N heapsort Memory: 26kB
12. 0.400 301,928.796 ↓ 12.7 76 1

GroupAggregate (cost=70,984.27..70,984.44 rows=6 width=68) (actual time=301,928.425..301,928.796 rows=76 loops=1)

  • Group Key: res_1."row
13. 0.209 301,928.396 ↓ 12.7 76 1

Sort (cost=70,984.27..70,984.29 rows=6 width=32) (actual time=301,928.391..301,928.396 rows=76 loops=1)

  • Sort Key: res_1."row
  • Sort Method: quicksort Memory: 36kB
14. 0.009 301,928.187 ↓ 12.7 76 1

Subquery Scan on res_1 (cost=70,983.85..70,984.20 rows=6 width=32) (actual time=301,927.901..301,928.187 rows=76 loops=1)

15. 0.475 301,928.178 ↓ 12.7 76 1

GroupAggregate (cost=70,983.85..70,984.14 rows=6 width=42) (actual time=301,927.900..301,928.178 rows=76 loops=1)

  • Group Key: res_2.grp_value, res_2.total
16. 0.597 301,927.703 ↓ 282.0 1,692 1

Sort (cost=70,983.85..70,983.87 rows=6 width=10) (actual time=301,927.597..301,927.703 rows=1,692 loops=1)

  • Sort Key: res_2.grp_value, res_2.total
  • Sort Method: quicksort Memory: 136kB
17. 0.167 301,927.106 ↓ 282.0 1,692 1

Subquery Scan on res_2 (cost=70,983.55..70,983.77 rows=6 width=10) (actual time=301,926.621..301,927.106 rows=1,692 loops=1)

18. 1.766 301,926.939 ↓ 282.0 1,692 1

WindowAgg (cost=70,983.55..70,983.71 rows=6 width=622) (actual time=301,926.620..301,926.939 rows=1,692 loops=1)

19. 2,248.310 301,925.173 ↓ 282.0 1,692 1

Unique (cost=70,983.55..70,983.58 rows=6 width=646) (actual time=296,174.748..301,925.173 rows=1,692 loops=1)

20. 16,603.527 299,676.863 ↓ 796,646.2 4,779,877 1

Sort (cost=70,983.55..70,983.56 rows=6 width=646) (actual time=296,174.746..299,676.863 rows=4,779,877 loops=1)

  • Sort Key: (ROW(pre_filter.anonymous_id, 'B_JDOUDOU'::text, raw_tracks_1.event, raw_tracks.utm_campaign, 'mt'::text))
  • Sort Method: external merge Disk: 430888kB
21. 1,815.841 283,073.336 ↓ 796,646.2 4,779,877 1

Nested Loop (cost=1.38..70,983.47 rows=6 width=646) (actual time=276.245..283,073.336 rows=4,779,877 loops=1)

22. 17.671 922.527 ↓ 10,184.0 10,184 1

Nested Loop (cost=0.69..80.90 rows=1 width=565) (actual time=275.828..922.527 rows=10,184 loops=1)

23. 284.600 284.600 ↓ 307.7 1,846 1

CTE Scan on pre_filter (cost=0.00..0.12 rows=6 width=516) (actual time=274.894..284.600 rows=1,846 loops=1)

24. 620.256 620.256 ↓ 6.0 6 1,846

Index Scan using pixel_key_anonymous_id_raw_tracks_next on raw_tracks_next raw_tracks (cost=0.69..13.45 rows=1 width=86) (actual time=0.164..0.336 rows=6 loops=1,846)

  • Index Cond: (((pixel_key)::text = 'B_JDOUDOU'::text) AND ((anonymous_id)::text = (pre_filter.anonymous_id)::text))
  • Filter: (((event)::text = 'pageview'::text) AND (("timestamp")::date >= apiv0_private.param_to_date('01/05/2019'::text)) AND (("timestamp")::date <= apiv0_private.param_to_date('05/09/2019'::text)))
  • Rows Removed by Filter: 4
25. 280,334.968 280,334.968 ↓ 78.2 469 10,184

Index Scan using pixel_key_timestamp_raw_tracks_next on raw_tracks_next raw_tracks_1 (cost=0.69..70,902.51 rows=6 width=58) (actual time=0.086..27.527 rows=469 loops=10,184)

  • Index Cond: (((pixel_key)::text = 'B_JDOUDOU'::text) AND ("timestamp" >= raw_tracks."timestamp") AND ("timestamp" <= (raw_tracks."timestamp" + '30 days'::interval)))
  • Filter: ((event)::text = 'sign_up'::text)
  • Rows Removed by Filter: 24084
Planning time : 0.790 ms
Execution time : 302,007.734 ms