explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mnmS

Settings
# exclusive inclusive rows x rows loops node
1. 82.946 7,515.046 ↑ 1.0 1 1

Aggregate (cost=274,609.39..274,609.40 rows=1 width=32) (actual time=7,515.044..7,515.046 rows=1 loops=1)

  • Buffers: shared hit=21463
2.          

CTE e1

3. 591.445 4,908.890 ↓ 1.1 129,251 1

Unique (cost=119,879.84..123,937.63 rows=114,128 width=111) (actual time=3,768.766..4,908.890 rows=129,251 loops=1)

  • Buffers: shared hit=18647
4. 3,588.354 4,317.445 ↑ 1.0 782,957 1

Sort (cost=119,879.84..121,908.73 rows=811,558 width=111) (actual time=3,768.761..4,317.445 rows=782,957 loops=1)

  • Sort Key: uba_defined_events.userid, uba_defined_events.visittime
  • Sort Method: quicksort Memory: 232549kB
  • Buffers: shared hit=18647
5. 729.091 729.091 ↑ 1.0 782,957 1

Index Only Scan using uba_defined_events_pkey on uba_defined_events (cost=0.68..40,224.06 rows=811,558 width=111) (actual time=0.037..729.091 rows=782,957 loops=1)

  • Index Cond: ((eventgroup = '6157f7503f7db0eb0be8f6b36d784ec2'::text) AND (siteid = '514-partycitycom'::text) AND (visittime >= '1545696000000'::bigint) AND (visittime < '1546300800000'::bigint))
  • Heap Fetches: 0
  • Buffers: shared hit=18647
6.          

CTE e2

7. 66.822 5,773.529 ↓ 5.1 35,996 1

GroupAggregate (cost=19,757.71..21,861.03 rows=7,046 width=45) (actual time=5,383.457..5,773.529 rows=35,996 loops=1)

  • Group Key: e2_1.userid
  • Buffers: shared hit=20077
8. 200.370 5,706.707 ↓ 8.0 56,696 1

Merge Join (cost=19,757.71..21,755.34 rows=7,046 width=41) (actual time=5,383.436..5,706.707 rows=56,696 loops=1)

  • Merge Cond: ((e2_1.userid)::text = (e1_1.userid)::text)
  • Join Filter: ((e2_1.visittime > e1_1.step1_time) AND (e2_1.visittime < (e1_1.step1_time + '2592000000'::bigint)))
  • Rows Removed by Join Filter: 441
  • Buffers: shared hit=20077
9. 176.150 225.729 ↑ 1.1 59,802 1

Sort (cost=7,888.24..8,046.85 rows=63,444 width=41) (actual time=187.034..225.729 rows=59,802 loops=1)

  • Sort Key: e2_1.userid
  • Sort Method: quicksort Memory: 6209kB
  • Buffers: shared hit=1430
10. 49.579 49.579 ↑ 1.1 59,802 1

Index Only Scan using uba_defined_events_pkey on uba_defined_events e2_1 (cost=0.68..2,827.56 rows=63,444 width=41) (actual time=0.032..49.579 rows=59,802 loops=1)

  • Index Cond: ((eventgroup = '2ee9baad493990b329d791d0ad6c32a9'::text) AND (siteid = '514-partycitycom'::text))
  • Heap Fetches: 0
  • Buffers: shared hit=1430
11. 195.088 5,280.608 ↓ 1.3 150,216 1

Sort (cost=11,869.48..12,154.80 rows=114,128 width=90) (actual time=5,196.387..5,280.608 rows=150,216 loops=1)

  • Sort Key: e1_1.userid
  • Sort Method: quicksort Memory: 13170kB
  • Buffers: shared hit=18647
12. 5,085.520 5,085.520 ↓ 1.1 129,251 1

CTE Scan on e1 e1_1 (cost=0.00..2,282.56 rows=114,128 width=90) (actual time=3,768.772..5,085.520 rows=129,251 loops=1)

  • Buffers: shared hit=18647
13.          

CTE e3

14. 33.489 950.088 ↓ 118.1 21,734 1

GroupAggregate (cost=11,391.38..11,394.60 rows=184 width=45) (actual time=901.683..950.088 rows=21,734 loops=1)

  • Group Key: e3_1.userid
  • Buffers: shared hit=650
15. 36.908 916.599 ↓ 145.8 26,820 1

Sort (cost=11,391.38..11,391.84 rows=184 width=41) (actual time=901.673..916.599 rows=26,820 loops=1)

  • Sort Key: e3_1.userid
  • Sort Method: quicksort Memory: 2864kB
  • Buffers: shared hit=650
16. 111.317 879.691 ↓ 145.8 26,820 1

Hash Join (cost=3,957.86..11,384.46 rows=184 width=41) (actual time=686.173..879.691 rows=26,820 loops=1)

  • Hash Cond: ((e1_2.userid)::text = (e3_1.userid)::text)
  • Join Filter: (e3_1.visittime < (e1_2.step1_time + '2592000000'::bigint))
  • Buffers: shared hit=650
17. 82.225 82.225 ↓ 1.1 129,251 1

CTE Scan on e1 e1_2 (cost=0.00..2,282.56 rows=114,128 width=90) (actual time=0.002..82.225 rows=129,251 loops=1)

18. 20.855 686.149 ↓ 48.6 26,820 1

Hash (cost=3,950.96..3,950.96 rows=552 width=123) (actual time=686.148..686.149 rows=26,820 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3216kB
  • Buffers: shared hit=650
19. 71.564 665.294 ↓ 48.6 26,820 1

Merge Join (cost=3,760.89..3,950.96 rows=552 width=123) (actual time=553.348..665.294 rows=26,820 loops=1)

  • Merge Cond: ((e3_1.userid)::text = (e2_2.userid)::text)
  • Join Filter: (e3_1.visittime > e2_2.step2_time)
  • Rows Removed by Join Filter: 9
  • Buffers: shared hit=650
20. 76.059 98.563 ↓ 1.0 27,020 1

Sort (cost=3,169.64..3,236.71 rows=26,828 width=41) (actual time=81.242..98.563 rows=27,020 loops=1)

  • Sort Key: e3_1.userid
  • Sort Method: quicksort Memory: 2879kB
  • Buffers: shared hit=650
21. 22.504 22.504 ↓ 1.0 27,020 1

Index Only Scan using uba_defined_events_pkey on uba_defined_events e3_1 (cost=0.68..1,196.24 rows=26,828 width=41) (actual time=0.032..22.504 rows=27,020 loops=1)

  • Index Cond: ((eventgroup = 'a6cfc74d9c2dbd7c793f3e77f98bbe92'::text) AND (siteid = '514-partycitycom'::text))
  • Heap Fetches: 0
  • Buffers: shared hit=650
22. 54.153 495.167 ↓ 5.8 41,090 1

Sort (cost=591.25..608.87 rows=7,046 width=90) (actual time=472.096..495.167 rows=41,090 loops=1)

  • Sort Key: e2_2.userid
  • Sort Method: quicksort Memory: 4349kB
23. 441.014 441.014 ↓ 5.1 35,996 1

CTE Scan on e2 e2_2 (cost=0.00..140.92 rows=7,046 width=90) (actual time=0.002..441.014 rows=35,996 loops=1)

24.          

CTE e4

25. 34.478 453.528 ↓ 3,549.8 21,299 1

GroupAggregate (cost=4,303.68..4,303.78 rows=6 width=45) (actual time=403.239..453.528 rows=21,299 loops=1)

  • Group Key: e4_1.userid
  • Buffers: shared hit=736
26. 39.112 419.050 ↓ 4,735.5 28,413 1

Sort (cost=4,303.68..4,303.69 rows=6 width=41) (actual time=403.226..419.050 rows=28,413 loops=1)

  • Sort Key: e4_1.userid
  • Sort Method: quicksort Memory: 2988kB
  • Buffers: shared hit=736
27. 113.722 379.938 ↓ 4,735.5 28,413 1

Hash Join (cost=1,592.79..4,303.60 rows=6 width=41) (actual time=183.875..379.938 rows=28,413 loops=1)

  • Hash Cond: ((e1_3.userid)::text = (e4_1.userid)::text)
  • Join Filter: (e4_1.visittime < (e1_3.step1_time + '2592000000'::bigint))
  • Buffers: shared hit=736
28. 82.365 82.365 ↓ 1.1 129,251 1

CTE Scan on e1 e1_3 (cost=0.00..2,282.56 rows=114,128 width=90) (actual time=0.002..82.365 rows=129,251 loops=1)

29. 22.547 183.851 ↓ 1,578.5 28,413 1

Hash (cost=1,592.56..1,592.56 rows=18 width=123) (actual time=183.850..183.851 rows=28,413 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3392kB
  • Buffers: shared hit=736
30. 40.701 161.304 ↓ 1,578.5 28,413 1

Hash Join (cost=6.66..1,592.56 rows=18 width=123) (actual time=95.126..161.304 rows=28,413 loops=1)

  • Hash Cond: ((e4_1.userid)::text = (e3_2.userid)::text)
  • Join Filter: (e4_1.visittime > e3_2.step3_time)
  • Rows Removed by Join Filter: 99
  • Buffers: shared hit=736
31. 25.523 25.523 ↑ 1.1 30,676 1

Index Only Scan using uba_defined_events_pkey on uba_defined_events e4_1 (cost=0.68..1,462.88 rows=32,810 width=41) (actual time=0.036..25.523 rows=30,676 loops=1)

  • Index Cond: ((eventgroup = 'f97618649cfda4a5f9d18d6810332a2c'::text) AND (siteid = '514-partycitycom'::text))
  • Heap Fetches: 0
  • Buffers: shared hit=736
32. 16.175 95.080 ↓ 118.1 21,734 1

Hash (cost=3.68..3.68 rows=184 width=90) (actual time=95.079..95.080 rows=21,734 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1954kB
33. 78.905 78.905 ↓ 118.1 21,734 1

CTE Scan on e3 e3_2 (cost=0.00..3.68 rows=184 width=90) (actual time=0.002..78.905 rows=21,734 loops=1)

34. 207.306 7,432.100 ↑ 31.1 129,251 1

Merge Right Join (cost=12,558.90..72,905.06 rows=4,020,729 width=16) (actual time=7,134.194..7,432.100 rows=129,251 loops=1)

  • Merge Cond: ((e2.userid)::text = (e1.userid)::text)
  • Buffers: shared hit=21463
35. 50.643 6,965.387 ↓ 5.1 35,996 1

Sort (cost=689.42..707.03 rows=7,046 width=94) (actual time=6,945.649..6,965.387 rows=35,996 loops=1)

  • Sort Key: e2.userid
  • Sort Method: quicksort Memory: 4349kB
  • Buffers: shared hit=21463
36. 48.697 6,914.744 ↓ 5.1 35,996 1

Hash Left Join (cost=6.93..239.09 rows=7,046 width=94) (actual time=6,843.203..6,914.744 rows=35,996 loops=1)

  • Hash Cond: ((e2.userid)::text = (e3.userid)::text)
  • Buffers: shared hit=21463
37. 5,406.320 5,406.320 ↓ 5.1 35,996 1

CTE Scan on e2 (cost=0.00..140.92 rows=7,046 width=86) (actual time=5,383.460..5,406.320 rows=35,996 loops=1)

  • Buffers: shared hit=20077
38. 16.287 1,459.727 ↓ 118.1 21,734 1

Hash (cost=4.63..4.63 rows=184 width=90) (actual time=1,459.726..1,459.727 rows=21,734 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1867kB
  • Buffers: shared hit=1386
39. 29.297 1,443.440 ↓ 118.1 21,734 1

Hash Left Join (cost=0.20..4.63 rows=184 width=90) (actual time=1,400.402..1,443.440 rows=21,734 loops=1)

  • Hash Cond: ((e3.userid)::text = (e4.userid)::text)
  • Buffers: shared hit=1386
40. 915.446 915.446 ↓ 118.1 21,734 1

CTE Scan on e3 (cost=0.00..3.68 rows=184 width=86) (actual time=901.690..915.446 rows=21,734 loops=1)

  • Buffers: shared hit=650
41. 15.585 498.697 ↓ 3,549.8 21,299 1

Hash (cost=0.12..0.12 rows=6 width=86) (actual time=498.696..498.697 rows=21,299 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1692kB
  • Buffers: shared hit=736
42. 483.112 483.112 ↓ 3,549.8 21,299 1

CTE Scan on e4 (cost=0.00..0.12 rows=6 width=86) (actual time=403.244..483.112 rows=21,299 loops=1)

  • Buffers: shared hit=736
43. 177.339 259.407 ↓ 1.1 129,251 1

Sort (cost=11,869.48..12,154.80 rows=114,128 width=86) (actual time=188.536..259.407 rows=129,251 loops=1)

  • Sort Key: e1.userid
  • Sort Method: quicksort Memory: 13170kB
44. 82.068 82.068 ↓ 1.1 129,251 1

CTE Scan on e1 (cost=0.00..2,282.56 rows=114,128 width=86) (actual time=0.002..82.068 rows=129,251 loops=1)