explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DSGE

Settings
# exclusive inclusive rows x rows loops node
1. 0.147 251.172 ↑ 31.2 32 1

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

2. 0.029 15.761 ↑ 31.2 32 1

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

3. 0.010 0.011 ↑ 1,000.0 1 1

ProjectSet (cost=0.00..20.07 rows=1,000 width=8) (actual time=0.008..0.011 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.411 15.721 ↓ 32.0 32 1

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

  • Filter: ((pp_h.rn = 1) AND (pp_h."actionType" = 'Approved'::"premiumPartnerActionTypes"))
  • Rows Removed by Filter: 4227
6. 9.009 15.310 ↓ 3.0 4,259 1

WindowAgg (cost=405.23..513.73 rows=1,400 width=48) (actual time=5.579..15.310 rows=4,259 loops=1)

7. 2.661 6.301 ↓ 3.0 4,259 1

Sort (cost=405.23..408.73 rows=1,400 width=24) (actual time=5.560..6.301 rows=4,259 loops=1)

  • Sort Key: hotel_premium_partner_actions."hotelId", hotel_premium_partner_actions."actionId" DESC
  • Sort Method: quicksort Memory: 525kB
8. 0.521 3.640 ↓ 3.0 4,259 1

Result (cost=0.00..332.07 rows=1,400 width=24) (actual time=0.062..3.640 rows=4,259 loops=1)

9. 0.367 3.119 ↓ 3.0 4,259 1

Append (cost=0.00..276.07 rows=1,400 width=24) (actual time=0.061..3.119 rows=4,259 loops=1)

10. 2.497 2.497 ↓ 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.060..2.497 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.255 0.255 ↓ 3.0 368 1

Seq Scan on hotel_premium_partner_requests (cost=0.00..41.00 rows=123 width=24) (actual time=0.013..0.255 rows=368 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. 18.560 235.264 ↑ 1.0 1 32

Aggregate (cost=357.26..357.30 rows=1 width=16) (actual time=7.352..7.352 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.872 216.704 ↓ 6.1 225 32

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

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

Append (cost=0.00..342.54 rows=37 width=29) (actual time=0.319..6.587 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. 39.296 39.296 ↓ 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.292..1.228 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. 169.344 169.344 ↓ 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.076..5.292 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.015 0.048 ↑ 1.0 83 1

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

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

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