explain.depesz.com

PostgreSQL's explain analyze made readable

Result: O8Bz

Settings
# exclusive inclusive rows x rows loops node
1. 0.191 244.960 ↑ 31.2 32 1

Nested Loop (cost=762.48..934,206.99 rows=1,000 width=32) (actual time=17.118..244.960 rows=32 loops=1)

2. 0.039 13.537 ↑ 31.2 32 1

Nested Loop (cost=405.23..576,826.05 rows=1,000 width=32) (actual time=4.540..13.537 rows=32 loops=1)

3. 0.027 0.028 ↑ 1,000.0 1 1

ProjectSet (cost=0.00..20.07 rows=1,000 width=8) (actual time=0.025..0.028 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.403 13.470 ↓ 32.0 32 1

Subquery Scan on pp_h (cost=405.23..576.73 rows=1 width=24) (actual time=4.512..13.470 rows=32 loops=1)

  • Filter: ((pp_h.rn = 1) AND (pp_h."actionType" = 'Approved'::"premiumPartnerActionTypes"))
  • Rows Removed by Filter: 4228
6. 7.900 13.067 ↓ 3.0 4,260 1

WindowAgg (cost=405.23..513.73 rows=1,400 width=48) (actual time=4.481..13.067 rows=4,260 loops=1)

7. 2.220 5.167 ↓ 3.0 4,260 1

Sort (cost=405.23..408.73 rows=1,400 width=24) (actual time=4.459..5.167 rows=4,260 loops=1)

  • Sort Key: hotel_premium_partner_actions."hotelId", hotel_premium_partner_actions."actionId" DESC
  • Sort Method: quicksort Memory: 525kB
8. 0.445 2.947 ↓ 3.0 4,260 1

Result (cost=0.00..332.07 rows=1,400 width=24) (actual time=0.020..2.947 rows=4,260 loops=1)

9. 0.356 2.502 ↓ 3.0 4,260 1

Append (cost=0.00..276.07 rows=1,400 width=24) (actual time=0.019..2.502 rows=4,260 loops=1)

10. 1.931 1.931 ↓ 3.0 3,891 1

Seq Scan on hotel_premium_partner_actions (cost=0.00..207.07 rows=1,277 width=24) (actual time=0.019..1.931 rows=3,891 loops=1)

  • Filter: ((partnership = ANY ('{Gold,Silver}'::"premiumPartnerPartnershipTypes"[])) 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)))
11. 0.215 0.215 ↓ 3.0 369 1

Seq Scan on hotel_premium_partner_requests (cost=0.00..41.00 rows=123 width=24) (actual time=0.014..0.215 rows=369 loops=1)

  • Filter: ((partnership = ANY ('{Gold,Silver}'::"premiumPartnerPartnershipTypes"[])) 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)))
12. 16.288 231.232 ↑ 1.0 1 32

Aggregate (cost=357.26..357.30 rows=1 width=16) (actual time=7.226..7.226 rows=1 loops=32)

  • Filter: ((sum(((COALESCE(r."reservationConfirmedPrice", r."reservationPrice"))::double precision * curr."currencyValue")) FILTER (WHERE ((r."reservationSubmitDate" >= (pp_h."submitDate" - '1 year'::interval)) AND (r."reservationDeparture" >= (pp_h."checkOutDate" - '1 year'::interval)) AND (r."reservationDeparture" < pp_h."checkOutDate"))) IS NOT NULL) OR (sum(((COALESCE(r."reservationConfirmedPrice", r."reservationPrice"))::double precision * curr."currencyValue")) FILTER (WHERE ((r."reservationSubmitDate" >= pp_h."submitDate") AND (r."reservationDeparture" >= pp_h."checkOutDate") AND (r."reservationDeparture" < (pp_h."checkOutDate" + '1 year'::interval)))) IS NOT NULL))
13. 5.395 214.944 ↓ 6.1 225 32

Hash Join (cost=11.85..354.48 rows=37 width=28) (actual time=0.294..6.717 rows=225 loops=32)

  • Hash Cond: (r."currencyID" = curr."currencyID")
14. 1.760 209.504 ↓ 6.1 225 32

Append (cost=0.00..342.54 rows=37 width=29) (actual time=0.290..6.547 rows=225 loops=32)

15. 0.032 0.032 ↓ 0.0 0 32

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

  • 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)) - '2 years'::interval)))
16. 36.512 36.512 ↓ 1.5 6 32

Index Scan using requests_hotel_submit_idx on requests r_1 (cost=0.43..137.74 rows=4 width=27) (actual time=0.265..1.141 rows=6 loops=32)

  • 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)) - '2 years'::interval)))
  • Filter: (("reservationEntryConfirmed" IS DISTINCT FROM false) AND ("reservationExtendedStatus" = 'ACTIVE'::"ReservationExtendedStatus"))
  • Rows Removed by Filter: 58
17. 171.200 171.200 ↓ 6.8 219 32

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.071..5.350 rows=219 loops=32)

  • 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)) - '2 years'::interval)))
  • Filter: (("reservationEntryConfirmed" IS DISTINCT FROM false) AND ("reservationExtendedStatus" = 'ACTIVE'::"ReservationExtendedStatus"))
  • Rows Removed by Filter: 76
18. 0.014 0.045 ↑ 1.0 83 1

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

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

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