explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uket

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 8.802 ↓ 0.0 0 1

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

2. 0.005 0.597 ↑ 166.7 6 1

Nested Loop (cost=234.95..235,413.84 rows=1,000 width=32) (actual time=0.555..0.597 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.010..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.005 0.580 ↓ 6.0 6 1

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

  • Filter: (pp_h.rn = 1)
6. 0.043 0.575 ↓ 2.0 6 1

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

7. 0.011 0.532 ↓ 2.0 6 1

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

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

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

9. 0.001 0.519 ↓ 2.0 6 1

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

10. 0.479 0.479 ↓ 3.0 6 1

Seq Scan on hotel_premium_partner_actions (cost=0.00..217.45 rows=2 width=20) (actual time=0.016..0.479 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.039 0.039 ↓ 0.0 0 1

Seq Scan on hotel_premium_partner_requests (cost=0.00..17.30 rows=1 width=20) (actual time=0.039..0.039 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.006 8.196 ↓ 0.0 0 6

Aggregate (cost=350.02..350.06 rows=1 width=16) (actual time=1.366..1.366 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.190 ↓ 0.0 0 6

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

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

Append (cost=0.00..341.94 rows=3 width=36) (actual time=1.364..1.364 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.412 2.412 ↓ 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.402..0.402 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.742 5.742 ↓ 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.957..0.957 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)