explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4fL7

Settings
# exclusive inclusive rows x rows loops node
1. 146.954 146.954 ↓ 25.0 25 1

CTE Scan on providers_near (cost=19,749.54..19,749.56 rows=1 width=36) (actual time=115.614..146.954 rows=25 loops=1)

2.          

CTE providers_near

3. 1.943 146.933 ↓ 25.0 25 1

Nested Loop Left Join (cost=19,475.05..19,749.54 rows=1 width=36) (actual time=115.611..146.933 rows=25 loops=1)

  • Join Filter: (user_provider.user_uid = (booking.provider[1]))
  • Rows Removed by Join Filter: 25,731
4. 1.781 24.740 ↓ 25.0 25 1

Nested Loop Left Join (cost=3,496.14..3,720.61 rows=1 width=52) (actual time=14.310..24.740 rows=25 loops=1)

  • Join Filter: (user_provider.user_uid = portfolio.user_uid)
  • Rows Removed by Join Filter: 31,417
  • Filter: (((CASE WHEN ('banned'::text = ANY (array_agg(portfolio.level))) THEN 2 WHEN ('top'::text = ANY (array_agg(portfolio.level))) THEN 0 WHEN ('default'::text = ANY (array_agg(portfolio.level))) THEN 1 ELSE NULL::integer END) IS NULL) OR ((CASE WHEN ('banned'::text = ANY (array_agg(portfolio.level))) THEN 2 WHEN ('top'::text = ANY (array_agg(portfolio.level))) THEN 0 WHEN ('default'::text = ANY (array_agg(portfolio.level))) THEN 1 ELSE NULL::integer END) <> 2))
5. 0.027 0.459 ↓ 25.0 25 1

Nested Loop (cost=4.90..124.97 rows=1 width=48) (actual time=0.104..0.459 rows=25 loops=1)

6. 0.247 0.307 ↓ 25.0 25 1

Bitmap Heap Scan on user_provider (cost=4.48..116.54 rows=1 width=16) (actual time=0.091..0.307 rows=25 loops=1)

  • Recheck Cond: (st_flipcoordinates(service_area) ~ '0101000020E61000009F2C6B51D53E5DC0A78AF3812E5D4040'::geometry)
  • Filter: (accept_bookings AND ((status)::text = 'active'::text) AND _st_contains(st_flipcoordinates(service_area), '0101000020E61000009F2C6B51D53E5DC0A78AF3812E5D4040'::geometry))
  • Rows Removed by Filter: 42
  • Heap Blocks: exact=66
7. 0.060 0.060 ↓ 2.5 67 1

Bitmap Index Scan on user_provider_service_area_idx (cost=0.00..4.48 rows=27 width=0) (actual time=0.060..0.060 rows=67 loops=1)

  • Index Cond: (st_flipcoordinates(service_area) ~ '0101000020E61000009F2C6B51D53E5DC0A78AF3812E5D4040'::geometry)
8. 0.125 0.125 ↑ 1.0 1 25

Index Scan using user_pkey on "user" (cost=0.42..8.44 rows=1 width=48) (actual time=0.005..0.005 rows=1 loops=25)

  • Index Cond: (uid = user_provider.user_uid)
9. 10.477 22.500 ↑ 1.0 1,257 25

HashAggregate (cost=3,491.24..3,563.01 rows=1,305 width=20) (actual time=0.527..0.900 rows=1,257 loops=25)

  • Group Key: portfolio.user_uid
10. 12.023 12.023 ↓ 1.0 1,393 1

Seq Scan on portfolio (cost=0.00..3,477.38 rows=1,386 width=23) (actual time=0.006..12.023 rows=1,393 loops=1)

  • Filter: ((code)::text = 'baby'::text)
  • Rows Removed by Filter: 50,651
11. 75.230 120.250 ↑ 1.3 1,030 25

HashAggregate (cost=15,978.91..15,998.70 rows=1,319 width=96) (actual time=4.032..4.810 rows=1,030 loops=25)

  • Group Key: booking.provider[1]
12. 41.708 45.020 ↓ 3.8 23,838 1

Bitmap Heap Scan on booking (cost=133.18..15,837.93 rows=6,266 width=110) (actual time=5.977..45.020 rows=23,838 loops=1)

  • Recheck Cond: ((((rating_service ->> 'value'::text))::numeric > '0'::numeric) AND (((rating_product ->> 'value'::text))::numeric > '0'::numeric) AND ((status)::text = ANY ('{paid,completed,refunded,cancelled}'::text[])))
  • Heap Blocks: exact=15,714
13. 3.312 3.312 ↓ 3.8 23,838 1

Bitmap Index Scan on booking_status_rating_service_partial_index_2 (cost=0.00..131.62 rows=6,266 width=0) (actual time=3.312..3.312 rows=23,838 loops=1)

Planning time : 1.435 ms
Execution time : 147.135 ms