explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6HV8

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 784.610 ↓ 10.0 10 1

Limit (cost=38,450.73..38,450.74 rows=1 width=80) (actual time=784.607..784.610 rows=10 loops=1)

2.          

CTE x

3. 14.237 765.811 ↑ 11.8 5,095 1

GroupAggregate (cost=33,479.48..35,138.31 rows=60,321 width=118) (actual time=749.766..765.811 rows=5,095 loops=1)

  • Group Key: e.creator_uuid, e.event_type, e.host_uuid, eh.possible_dates
4. 24.677 751.574 ↑ 5.8 10,429 1

Sort (cost=33,479.48..33,630.28 rows=60,321 width=102) (actual time=749.735..751.574 rows=10,429 loops=1)

  • Sort Key: e.creator_uuid, e.event_type, e.host_uuid, eh.possible_dates
  • Sort Method: quicksort Memory: 1878kB
5. 30.473 726.897 ↑ 5.8 10,429 1

Hash Join (cost=8,026.79..25,389.38 rows=60,321 width=102) (actual time=276.232..726.897 rows=10,429 loops=1)

  • Hash Cond: (ah.artist_uuid = a_1.artist_uuid)
  • Join Filter: (e.creator_uuid <> a_1.person_uuid)
  • Rows Removed by Join Filter: 74405
6. 32.030 692.956 ↓ 1.4 84,834 1

Hash Join (cost=6,642.27..23,846.32 rows=60,333 width=118) (actual time=272.742..692.956 rows=84,834 loops=1)

  • Hash Cond: (e.artist_uuid = ah.artist_uuid)
7. 85.749 624.592 ↓ 1.3 88,443 1

Hash Join (cost=6,296.17..21,820.61 rows=66,233 width=102) (actual time=236.391..624.592 rows=88,443 loops=1)

  • Hash Cond: (eh.event_uuid = e.event_uuid)
8. 302.613 302.613 ↓ 1.3 89,171 1

Seq Scan on event_history eh (cost=0.00..12,683.26 rows=66,730 width=59) (actual time=0.030..302.613 rows=89,171 loops=1)

  • Filter: ((event_state <> 'created'::citext) AND ((now())::timestamp without time zone <@ effective))
  • Rows Removed by Filter: 202722
9. 78.995 236.230 ↑ 1.0 96,949 1

Hash (cost=3,851.78..3,851.78 rows=96,991 width=75) (actual time=236.230..236.230 rows=96,949 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 3062kB
10. 157.235 157.235 ↑ 1.0 96,949 1

Seq Scan on events e (cost=0.00..3,851.78 rows=96,991 width=75) (actual time=0.023..157.235 rows=96,949 loops=1)

  • Filter: ((event_type <> 'contest'::citext) AND (event_type <> 'charity'::citext))
  • Rows Removed by Filter: 733
11. 25.416 36.334 ↓ 1.0 3,240 1

Hash (cost=306.69..306.69 rows=3,152 width=16) (actual time=36.333..36.334 rows=3,240 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 184kB
12. 10.918 10.918 ↓ 1.0 3,240 1

Seq Scan on artist_history ah (cost=0.00..306.69 rows=3,152 width=16) (actual time=0.024..10.918 rows=3,240 loops=1)

  • Filter: ((artist_state <> 'created'::citext) AND (artist_state <> 'removed'::citext) AND ((now())::timestamp without time zone <@ effective))
  • Rows Removed by Filter: 5125
13. 0.960 3.468 ↓ 1.0 4,924 1

Hash (cost=1,323.12..1,323.12 rows=4,912 width=32) (actual time=3.468..3.468 rows=4,924 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 372kB
14. 2.508 2.508 ↓ 1.0 4,924 1

Seq Scan on artists a_1 (cost=0.00..1,323.12 rows=4,912 width=32) (actual time=0.011..2.508 rows=4,924 loops=1)

15.          

CTE y

16. 769.619 769.619 ↑ 6.4 47 1

CTE Scan on x (cost=0.00..1,960.43 rows=302 width=32) (actual time=750.366..769.619 rows=47 loops=1)

  • Filter: ('ef1d9b73-e94e-4891-8dd8-44eb8b1a44ba'::uuid = ANY (artist_array))
  • Rows Removed by Filter: 5048
17.          

CTE z

18. 0.145 769.793 ↑ 188.8 160 1

ProjectSet (cost=0.00..159.31 rows=30,200 width=16) (actual time=750.377..769.793 rows=160 loops=1)

19. 769.648 769.648 ↑ 6.4 47 1

CTE Scan on y (cost=0.00..6.04 rows=302 width=32) (actual time=750.368..769.648 rows=47 loops=1)

20.          

CTE b

21. 0.017 0.790 ↑ 1.0 6 1

Nested Loop (cost=0.28..194.25 rows=6 width=58) (actual time=0.750..0.790 rows=6 loops=1)

22. 0.725 0.725 ↑ 1.0 6 1

Seq Scan on artist_locations al (cost=0.00..144.45 rows=6 width=16) (actual time=0.720..0.725 rows=6 loops=1)

  • Filter: (artist_uuid = 'ef1d9b73-e94e-4891-8dd8-44eb8b1a44ba'::uuid)
  • Rows Removed by Filter: 6923
23. 0.048 0.048 ↑ 1.0 1 6

Index Scan using locations_pkey on locations l (cost=0.28..8.30 rows=1 width=74) (actual time=0.008..0.008 rows=1 loops=6)

  • Index Cond: (location_uuid = al.location_uuid)
24.          

CTE a

25. 0.169 784.515 ↓ 65.0 65 1

Unique (cost=998.13..998.41 rows=1 width=126) (actual time=783.461..784.515 rows=65 loops=1)

26. 1.185 784.346 ↓ 116.0 116 1

GroupAggregate (cost=998.13..998.40 rows=1 width=126) (actual time=783.459..784.346 rows=116 loops=1)

  • Group Key: a_2.artist_name, b.location_name, b.location, l_1.location
27. 1.557 783.161 ↓ 199.0 199 1

Sort (cost=998.13..998.13 rows=1 width=110) (actual time=783.130..783.161 rows=199 loops=1)

  • Sort Key: a_2.artist_name, b.location_name, b.location, l_1.location
  • Sort Method: quicksort Memory: 52kB
28. 0.210 781.604 ↓ 199.0 199 1

Nested Loop (cost=205.62..998.12 rows=1 width=110) (actual time=762.135..781.604 rows=199 loops=1)

29. 0.146 780.797 ↓ 199.0 199 1

Hash Join (cost=205.34..997.64 rows=1 width=128) (actual time=762.117..780.797 rows=199 loops=1)

  • Hash Cond: (z.artist_uuid = al_1.artist_uuid)
30. 769.870 769.870 ↑ 268.3 112 1

CTE Scan on z (cost=0.00..679.50 rows=30,049 width=16) (actual time=751.319..769.870 rows=112 loops=1)

  • Filter: (artist_uuid <> 'ef1d9b73-e94e-4891-8dd8-44eb8b1a44ba'::uuid)
  • Rows Removed by Filter: 48
31. 0.639 10.781 ↓ 1,307.5 2,615 1

Hash (cost=205.31..205.31 rows=2 width=112) (actual time=10.781..10.781 rows=2,615 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 333kB
32. 1.539 10.142 ↓ 1,307.5 2,615 1

Hash Join (cost=52.20..205.31 rows=2 width=112) (actual time=7.898..10.142 rows=2,615 loops=1)

  • Hash Cond: (al_1.location_uuid = l_1.location_uuid)
33. 0.746 0.746 ↓ 1.0 6,929 1

Seq Scan on artist_locations al_1 (cost=0.00..127.16 rows=6,916 width=32) (actual time=0.028..0.746 rows=6,929 loops=1)

34. 0.546 7.857 ↓ 1,126.0 1,126 1

Hash (cost=52.18..52.18 rows=1 width=112) (actual time=7.857..7.857 rows=1,126 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 146kB
35. 0.304 7.311 ↓ 1,126.0 1,126 1

Nested Loop (cost=0.27..52.18 rows=1 width=112) (actual time=1.171..7.311 rows=1,126 loops=1)

36. 0.797 0.797 ↑ 1.0 6 1

CTE Scan on b (cost=0.00..0.12 rows=6 width=64) (actual time=0.752..0.797 rows=6 loops=1)

37. 6.210 6.210 ↓ 188.0 188 6

Index Scan using locations_gix on locations l_1 (cost=0.27..8.67 rows=1 width=48) (actual time=0.108..1.035 rows=188 loops=6)

  • Index Cond: (location && _st_expand(b.location, '80000'::double precision))
  • Filter: ((b.location && _st_expand(location, '80000'::double precision)) AND _st_dwithin(b.location, location, '80000'::double precision, true))
  • Rows Removed by Filter: 51
38. 0.597 0.597 ↑ 1.0 1 199

Index Scan using artists_pkey on artists a_2 (cost=0.28..0.48 rows=1 width=30) (actual time=0.003..0.003 rows=1 loops=199)

  • Index Cond: (artist_uuid = z.artist_uuid)
39. 0.049 784.606 ↓ 10.0 10 1

Sort (cost=0.03..0.04 rows=1 width=80) (actual time=784.606..784.606 rows=10 loops=1)

  • Sort Key: a.rating DESC, a.distance
  • Sort Method: top-N heapsort Memory: 26kB
40. 784.557 784.557 ↓ 65.0 65 1

CTE Scan on a (cost=0.00..0.02 rows=1 width=80) (actual time=783.466..784.557 rows=65 loops=1)

Planning time : 2.785 ms
Execution time : 785.140 ms