explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WgDE

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 415.180 ↓ 0.0 0 1

Limit (cost=11,663.67..11,663.67 rows=1 width=346) (actual time=415.180..415.180 rows=0 loops=1)

2. 0.006 415.180 ↓ 0.0 0 1

Sort (cost=11,663.67..11,663.67 rows=1 width=346) (actual time=415.180..415.180 rows=0 loops=1)

  • Sort Key: (avg((((booking.rating_service ->> 'value'::text))::numeric))) DESC NULLS LAST
  • Sort Method: quicksort Memory: 25kB
3. 0.000 415.174 ↓ 0.0 0 1

Nested Loop Left Join (cost=11,643.46..11,663.66 rows=1 width=346) (actual time=415.174..415.174 rows=0 loops=1)

  • Join Filter: (user_provider.user_uid = (booking.provider[1]))
  • Filter: (((avg((((booking.rating_service ->> 'value'::text))::numeric))) >= '4'::numeric) OR ((avg((((booking.rating_service ->> 'value'::text))::numeric))) IS NULL))
4. 0.001 415.174 ↓ 0.0 0 1

Nested Loop (cost=3,568.38..3,581.06 rows=1 width=447) (actual time=415.173..415.174 rows=0 loops=1)

5. 0.001 415.173 ↓ 0.0 0 1

Nested Loop (cost=3,568.24..3,580.63 rows=1 width=470) (actual time=415.173..415.173 rows=0 loops=1)

6. 0.012 415.157 ↓ 3.0 3 1

Merge Join (cost=3,567.95..3,580.26 rows=1 width=157) (actual time=415.120..415.157 rows=3 loops=1)

  • Merge Cond: (portfolio.user_uid = "user".uid)
7. 0.026 1.458 ↑ 1.2 226 1

Group (cost=1,089.89..1,091.23 rows=267 width=16) (actual time=1.412..1.458 rows=226 loops=1)

  • Group Key: portfolio.user_uid
8. 0.095 1.432 ↑ 1.2 226 1

Sort (cost=1,089.89..1,090.56 rows=267 width=16) (actual time=1.411..1.432 rows=226 loops=1)

  • Sort Key: portfolio.user_uid
  • Sort Method: quicksort Memory: 36kB
9. 0.013 1.337 ↑ 1.1 241 1

Nested Loop (cost=371.93..1,079.13 rows=267 width=16) (actual time=1.083..1.337 rows=241 loops=1)

10. 0.010 0.010 ↑ 1.0 1 1

Index Scan using shoottype_slug_unique on shoottype (cost=0.14..2.36 rows=1 width=8) (actual time=0.008..0.010 rows=1 loops=1)

  • Index Cond: (slug = 'anniversary'::text)
11. 0.269 1.314 ↑ 1.3 241 1

Bitmap Heap Scan on portfolio (cost=371.79..1,073.67 rows=311 width=23) (actual time=1.071..1.314 rows=241 loops=1)

  • Recheck Cond: ((code)::text = shoottype.name)
  • Filter: (array_length(images, 1) > 0)
  • Heap Blocks: exact=226
12. 1.045 1.045 ↑ 3.9 241 1

Bitmap Index Scan on portfolio_user_uid_code_index (cost=0.00..371.71 rows=933 width=0) (actual time=1.045..1.045 rows=241 loops=1)

  • Index Cond: ((code)::text = shoottype.name)
13. 0.006 413.687 ↑ 1.0 3 1

Materialize (cost=2,478.06..2,485.68 rows=3 width=141) (actual time=413.673..413.687 rows=3 loops=1)

14. 0.003 413.681 ↑ 1.0 3 1

Nested Loop (cost=2,478.06..2,485.67 rows=3 width=141) (actual time=413.669..413.681 rows=3 loops=1)

15. 0.010 413.660 ↑ 1.0 3 1

GroupAggregate (cost=2,477.64..2,477.70 rows=3 width=48) (actual time=413.657..413.660 rows=3 loops=1)

  • Group Key: unnested_portfolio.user_uid
16. 0.007 413.650 ↑ 1.0 3 1

Sort (cost=2,477.64..2,477.65 rows=3 width=32) (actual time=413.649..413.650 rows=3 loops=1)

  • Sort Key: unnested_portfolio.user_uid
  • Sort Method: quicksort Memory: 25kB
17. 0.002 413.643 ↑ 1.0 3 1

Subquery Scan on unnested_portfolio (cost=2,477.57..2,477.62 rows=3 width=32) (actual time=413.640..413.643 rows=3 loops=1)

18. 0.002 413.641 ↑ 1.0 3 1

Limit (cost=2,477.57..2,477.59 rows=3 width=43) (actual time=413.640..413.641 rows=3 loops=1)

19. 25.199 413.639 ↑ 745,966.7 3 1

Sort (cost=2,477.57..13,611.13 rows=2,237,900 width=43) (actual time=413.638..413.639 rows=3 loops=1)

  • Sort Key: (CASE WHEN ((portfolio_1.code)::text = 'anniversary'::text) THEN 1 ELSE 2 END)
  • Sort Method: top-N heapsort Memory: 25kB
20. 112.997 388.440 ↓ 8.5 190,949 1

HashAggregate (cost=1,740.75..2,188.33 rows=22,379 width=43) (actual time=348.164..388.440 rows=190,949 loops=1)

  • Group Key: portfolio_1.user_uid, portfolio_1.code, (unnest(portfolio_1.images))::uuid
21. 275.443 275.443 ↓ 8.5 191,009 1

Seq Scan on portfolio portfolio_1 (cost=0.00..1,572.83 rows=22,390 width=39) (actual time=0.013..275.443 rows=191,009 loops=1)

22. 0.018 0.018 ↑ 1.0 1 3

Index Scan using user_pkey on "user" (cost=0.42..2.64 rows=1 width=93) (actual time=0.006..0.006 rows=1 loops=3)

  • Index Cond: (uid = unnested_portfolio.user_uid)
23. 0.015 0.015 ↓ 0.0 0 3

Index Scan using user_provider_user_uid_index on user_provider (cost=0.29..0.36 rows=1 width=345) (actual time=0.005..0.005 rows=0 loops=3)

  • Index Cond: (user_uid = "user".uid)
  • Filter: ((webflow_slug IS NOT NULL) AND (((status)::text = 'active'::text) OR (((status)::text = 'approved'::text) AND (backlink_status <> 'not_requested'::text))))
  • Rows Removed by Filter: 1
24. 0.000 0.000 ↓ 0.0 0

Index Scan using location_polygon_idx on location (cost=0.14..0.43 rows=1 width=1,401) (never executed)

  • Index Cond: (st_flipcoordinates("user".point) && st_flipcoordinates(polygon))
  • Filter: ((slug = 'san-francisco-ca'::text) AND _st_intersects(st_flipcoordinates("user".point), st_flipcoordinates(polygon)))
25. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=8,075.08..8,077.58 rows=200 width=56) (never executed)

  • Group Key: (booking.provider[1])
26. 0.000 0.000 ↓ 0.0 0

Append (cost=0.00..8,040.19 rows=6,978 width=48) (never executed)

27. 0.000 0.000 ↓ 0.0 0

Seq Scan on booking (cost=0.00..7,873.56 rows=5,961 width=48) (never executed)

  • Filter: (((status)::text = ANY ('{paid,completed,refunded,cancelled}'::text[])) AND (((rating_service ->> 'value'::text))::numeric > '0'::numeric))
28. 0.000 0.000 ↓ 0.0 0

Seq Scan on external_review (cost=0.00..96.85 rows=1,017 width=48) (never executed)

  • Filter: (rating IS NOT NULL)