explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qPnV

Settings
# exclusive inclusive rows x rows loops node
1. 0.030 76.248 ↑ 66.7 15 1

Nested Loop Left Join (cost=614.65..615,111.01 rows=1,000 width=64) (actual time=9.633..76.248 rows=15 loops=1)

2. 0.010 1.158 ↑ 66.7 15 1

Nested Loop (cost=258.78..259,120.07 rows=1,000 width=48) (actual time=1.035..1.158 rows=15 loops=1)

3. 0.009 0.010 ↑ 1,000.0 1 1

ProjectSet (cost=0.00..20.07 rows=1,000 width=8) (actual time=0.008..0.010 rows=1 loops=1)

4. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.04 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

5. 0.009 1.138 ↓ 15.0 15 1

Subquery Scan on pp_h (cost=258.78..259.02 rows=1 width=40) (actual time=1.024..1.138 rows=15 loops=1)

  • Filter: (pp_h.rn = 1)
6. 0.118 1.129 ↓ 7.5 15 1

WindowAgg (cost=258.78..258.93 rows=2 width=44) (actual time=1.023..1.129 rows=15 loops=1)

7. 0.026 1.011 ↓ 7.5 15 1

Sort (cost=258.78..258.78 rows=2 width=20) (actual time=1.003..1.011 rows=15 loops=1)

  • Sort Key: hotel_premium_partner_actions."hotelId", hotel_premium_partner_actions."actionId" DESC
  • Sort Method: quicksort Memory: 26kB
8. 0.003 0.985 ↓ 7.5 15 1

Result (cost=0.00..258.77 rows=2 width=20) (actual time=0.029..0.985 rows=15 loops=1)

9. 0.003 0.982 ↓ 7.5 15 1

Append (cost=0.00..258.69 rows=2 width=20) (actual time=0.028..0.982 rows=15 loops=1)

10. 0.897 0.897 ↓ 15.0 15 1

Seq Scan on hotel_premium_partner_actions (cost=0.00..216.65 rows=1 width=20) (actual time=0.027..0.897 rows=15 loops=1)

  • Filter: ((partnership = ANY ('{Gold,Silver}'::"premiumPartnerPartnershipTypes"[])) AND ("actionType" = 'Approved'::"premiumPartnerActionTypes") AND ("actionDate" < ((generate_series('2019-01-01 00:00:00+01'::timestamp with time zone, date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone), '1 year'::interval)) + '1 year'::interval)))
  • Rows Removed by Filter: 3857
11. 0.082 0.082 ↓ 0.0 0 1

Seq Scan on hotel_premium_partner_requests (cost=0.00..42.00 rows=1 width=20) (actual time=0.082..0.082 rows=0 loops=1)

  • Filter: ((partnership = ANY ('{Gold,Silver}'::"premiumPartnerPartnershipTypes"[])) AND ("actionType" = 'Approved'::"premiumPartnerActionTypes") AND ("actionDate" < ((generate_series('2019-01-01 00:00:00+01'::timestamp with time zone, date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone), '1 year'::interval)) + '1 year'::interval)))
  • Rows Removed by Filter: 354
12. 7.725 75.060 ↑ 1.0 1 15

Aggregate (cost=355.87..355.91 rows=1 width=16) (actual time=5.004..5.004 rows=1 loops=15)

13. 2.092 67.335 ↓ 5.3 196 15

Hash Join (cost=11.85..354.48 rows=37 width=28) (actual time=0.251..4.489 rows=196 loops=15)

  • Hash Cond: (r."currencyID" = curr."currencyID")
14. 0.630 65.190 ↓ 5.3 196 15

Append (cost=0.00..342.54 rows=37 width=29) (actual time=0.244..4.346 rows=196 loops=15)

15. 0.015 0.015 ↓ 0.0 0 15

Seq Scan on reservations r (cost=0.00..0.00 rows=1 width=52) (actual time=0.001..0.001 rows=0 loops=15)

  • Filter: (("reservationEntryConfirmed" IS DISTINCT FROM false) AND ("hotelID" = pp_h."hotelId") AND ("reservationExtendedStatus" = 'ACTIVE'::"ReservationExtendedStatus") AND ("reservationSubmitDate" >= ((generate_series('2019-01-01 00:00:00+01'::timestamp with time zone, date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone), '1 year'::interval)) - '1 year'::interval)))
16. 10.980 10.980 ↑ 1.0 4 15

Index Scan using requests_hotel_submit_idx on requests r_1 (cost=0.43..137.74 rows=4 width=27) (actual time=0.219..0.732 rows=4 loops=15)

  • Index Cond: (("hotelID" = pp_h."hotelId") AND ("reservationSubmitDate" >= ((generate_series('2019-01-01 00:00:00+01'::timestamp with time zone, date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone), '1 year'::interval)) - '1 year'::interval)))
  • Filter: (("reservationEntryConfirmed" IS DISTINCT FROM false) AND ("reservationExtendedStatus" = 'ACTIVE'::"ReservationExtendedStatus"))
  • Rows Removed by Filter: 44
17. 53.565 53.565 ↓ 6.0 192 15

Index Scan using room_reservations_hotel_submit_idx on room_reservations r_2 (cost=0.43..204.05 rows=32 width=28) (actual time=0.059..3.571 rows=192 loops=15)

  • Index Cond: (("hotelID" = pp_h."hotelId") AND ("reservationSubmitDate" >= ((generate_series('2019-01-01 00:00:00+01'::timestamp with time zone, date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone), '1 year'::interval)) - '1 year'::interval)))
  • Filter: (("reservationEntryConfirmed" IS DISTINCT FROM false) AND ("reservationExtendedStatus" = 'ACTIVE'::"ReservationExtendedStatus"))
  • Rows Removed by Filter: 55
18. 0.023 0.053 ↑ 1.0 83 1

Hash (cost=8.32..8.32 rows=83 width=8) (actual time=0.053..0.053 rows=83 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
19. 0.030 0.030 ↑ 1.0 83 1

Seq Scan on currencies curr (cost=0.00..8.32 rows=83 width=8) (actual time=0.007..0.030 rows=83 loops=1)