explain.depesz.com

PostgreSQL's explain analyze made readable

Result: usXF

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

CTE Scan on providers_near (cost=32,260.63..32,260.65 rows=1 width=36) (actual time=99.210..101.229 rows=25 loops=1)

2.          

CTE providers_near

3. 0.541 101.215 ↓ 25.0 25 1

Hash Right Join (cost=32,222.14..32,260.63 rows=1 width=36) (actual time=99.207..101.215 rows=25 loops=1)

  • Hash Cond: ((booking.provider[1]) = user_provider.user_uid)
4. 36.106 88.724 ↓ 1.2 1,550 1

HashAggregate (cost=29,560.32..29,580.11 rows=1,319 width=96) (actual time=86.943..88.724 rows=1,550 loops=1)

  • Group Key: booking.provider[1]
5. 49.758 52.618 ↓ 3.8 23,838 1

Bitmap Heap Scan on booking (cost=443.69..29,419.34 rows=6,266 width=110) (actual time=5.043..52.618 rows=23,838 loops=1)

  • Recheck Cond: (((status)::text = ANY ('{paid,completed,refunded,cancelled}'::text[])) AND (((rating_service ->> 'value'::text))::numeric > '0'::numeric))
  • Filter: (((rating_product ->> 'value'::text))::numeric > '0'::numeric)
  • Rows Removed by Filter: 64
  • Heap Blocks: exact=15,742
6. 2.860 2.860 ↓ 1.3 23,904 1

Bitmap Index Scan on booking_status_rating_service_partial_index (cost=0.00..442.12 rows=18,797 width=0) (actual time=2.860..2.860 rows=23,904 loops=1)

  • Index Cond: ((status)::text = ANY ('{paid,completed,refunded,cancelled}'::text[]))
7. 0.011 11.950 ↓ 25.0 25 1

Hash (cost=2,661.81..2,661.81 rows=1 width=52) (actual time=11.950..11.950 rows=25 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
8. 1.726 11.939 ↓ 25.0 25 1

Nested Loop Left Join (cost=2,437.34..2,661.81 rows=1 width=52) (actual time=2.459..11.939 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))
9. 0.014 0.338 ↓ 25.0 25 1

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

10. 0.191 0.224 ↓ 25.0 25 1

Bitmap Heap Scan on user_provider (cost=4.48..116.54 rows=1 width=16) (actual time=0.056..0.224 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
11. 0.033 0.033 ↓ 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.033..0.033 rows=67 loops=1)

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

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

  • Index Cond: (uid = user_provider.user_uid)
13. 8.583 9.875 ↑ 1.0 1,257 25

HashAggregate (cost=2,432.43..2,504.21 rows=1,305 width=20) (actual time=0.077..0.395 rows=1,257 loops=25)

  • Group Key: portfolio.user_uid
14. 1.115 1.292 ↓ 1.0 1,393 1

Bitmap Heap Scan on portfolio (cost=43.16..2,418.57 rows=1,386 width=23) (actual time=0.283..1.292 rows=1,393 loops=1)

  • Recheck Cond: ((code)::text = 'baby'::text)
  • Heap Blocks: exact=1,096
15. 0.177 0.177 ↓ 1.0 1,393 1

Bitmap Index Scan on portfolio_code_user_uid_idx (cost=0.00..42.81 rows=1,386 width=0) (actual time=0.177..0.177 rows=1,393 loops=1)

  • Index Cond: ((code)::text = 'baby'::text)
Planning time : 0.797 ms
Execution time : 101.366 ms