explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RdQ3

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 8.972 ↓ 0.0 0 1

Nested Loop (cost=584.98..585,559.69 rows=1,000 width=32) (actual time=8.972..8.972 rows=0 loops=1)

2. 0.006 0.595 ↑ 166.7 6 1

Nested Loop (cost=234.95..235,413.84 rows=1,000 width=32) (actual time=0.544..0.595 rows=6 loops=1)

3. 0.011 0.012 ↑ 1,000.0 1 1

ProjectSet (cost=0.00..20.07 rows=1,000 width=8) (actual time=0.009..0.012 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.006 0.577 ↓ 6.0 6 1

Subquery Scan on pp_h (cost=234.95..235.31 rows=1 width=24) (actual time=0.533..0.577 rows=6 loops=1)

  • Filter: (pp_h.rn = 1)
6. 0.048 0.571 ↓ 2.0 6 1

WindowAgg (cost=234.95..235.19 rows=3 width=44) (actual time=0.531..0.571 rows=6 loops=1)

7. 0.012 0.523 ↓ 2.0 6 1

Sort (cost=234.95..234.96 rows=3 width=20) (actual time=0.520..0.523 rows=6 loops=1)

  • Sort Key: hotel_premium_partner_actions."hotelId", hotel_premium_partner_actions."actionId" DESC
  • Sort Method: quicksort Memory: 25kB
8. 0.001 0.511 ↓ 2.0 6 1

Result (cost=0.00..234.93 rows=3 width=20) (actual time=0.016..0.511 rows=6 loops=1)

9. 0.002 0.510 ↓ 2.0 6 1

Append (cost=0.00..234.81 rows=3 width=20) (actual time=0.016..0.510 rows=6 loops=1)

10. 0.467 0.467 ↓ 3.0 6 1

Seq Scan on hotel_premium_partner_actions (cost=0.00..217.45 rows=2 width=20) (actual time=0.015..0.467 rows=6 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: 3843
11. 0.041 0.041 ↓ 0.0 0 1

Seq Scan on hotel_premium_partner_requests (cost=0.00..17.30 rows=1 width=20) (actual time=0.041..0.041 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: 267
12. 0.012 8.370 ↓ 0.0 0 6

Aggregate (cost=350.02..350.06 rows=1 width=16) (actual time=1.395..1.395 rows=0 loops=6)

  • 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))
  • Rows Removed by Filter: 1
13. 0.006 8.358 ↓ 0.0 0 6

Hash Join (cost=7.85..349.80 rows=3 width=36) (actual time=1.393..1.393 rows=0 loops=6)

  • Hash Cond: (r."currencyID" = curr."currencyID")
14. 0.036 8.352 ↓ 0.0 0 6

Append (cost=0.00..341.94 rows=3 width=36) (actual time=1.392..1.392 rows=0 loops=6)

15. 0.000 0.000 ↓ 0.0 0 6

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

  • Filter: (("reservationEntryConfirmed" IS DISTINCT FROM false) AND ("hotelID" = pp_h."hotelId") AND ("foreignPartnerID" = 2) 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. 2.472 2.472 ↓ 0.0 0 6

Index Scan using requests_hotel_submit_idx on requests r_1 (cost=0.43..137.75 rows=1 width=27) (actual time=0.412..0.412 rows=0 loops=6)

  • 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 ("foreignPartnerID" = 2) AND ("reservationExtendedStatus" = 'ACTIVE'::"ReservationExtendedStatus"))
  • Rows Removed by Filter: 219
17. 5.844 5.844 ↓ 0.0 0 6

Index Scan using room_reservations_hotel_submit_idx on room_reservations r_2 (cost=0.43..204.13 rows=1 width=28) (actual time=0.974..0.974 rows=0 loops=6)

  • 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 ("foreignPartnerID" = 2) AND ("reservationExtendedStatus" = 'ACTIVE'::"ReservationExtendedStatus"))
  • Rows Removed by Filter: 583
18. 0.000 0.000 ↓ 0.0 0

Hash (cost=4.32..4.32 rows=83 width=8) (never executed)

19. 0.000 0.000 ↓ 0.0 0

Seq Scan on currencies curr (cost=0.00..4.32 rows=83 width=8) (never executed)