explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4For

Settings
# exclusive inclusive rows x rows loops node
1. 2.594 53.651 ↑ 1.8 3,844 1

Hash Left Join (cost=8,065.82..581,590.02 rows=7,000 width=129) (actual time=34.077..53.651 rows=3,844 loops=1)

  • Hash Cond: ((pp_states."hotelId" = sgd."hotelId") AND (date_part('year'::text, (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))) = sgd.year))
2. 1.426 51.032 ↑ 1.8 3,844 1

Hash Left Join (cost=7,940.22..575,845.32 rows=7,000 width=82) (actual time=34.024..51.032 rows=3,844 loops=1)

  • Hash Cond: (pp_states."accountId" = acc."accountID")
3. 3.771 20.591 ↑ 1.8 3,844 1

Nested Loop (cost=378.70..568,265.43 rows=7,000 width=77) (actual time=4.443..20.591 rows=3,844 loops=1)

4. 0.485 9.132 ↑ 1.8 3,844 1

Nested Loop (cost=378.28..508,957.93 rows=7,000 width=32) (actual time=4.427..9.132 rows=3,844 loops=1)

5. 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)

6. 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)

7. 0.499 8.635 ↓ 549.1 3,844 1

Subquery Scan on pp_states (cost=378.28..508.62 rows=7 width=24) (actual time=4.416..8.635 rows=3,844 loops=1)

  • Filter: (pp_states.rn = 1)
  • Rows Removed by Filter: 272
8. 3.165 8.136 ↓ 3.0 4,116 1

WindowAgg (cost=378.28..450.31 rows=1,372 width=36) (actual time=4.413..8.136 rows=4,116 loops=1)

9. 2.229 4.971 ↓ 3.0 4,116 1

Sort (cost=378.28..381.71 rows=1,372 width=28) (actual time=4.405..4.971 rows=4,116 loops=1)

  • Sort Key: hotel_premium_partner_actions."hotelId", hotel_premium_partner_actions.partnership, hotel_premium_partner_actions."actionId" DESC
  • Sort Method: quicksort Memory: 514kB
10. 0.469 2.742 ↓ 3.0 4,116 1

Result (cost=0.00..306.78 rows=1,372 width=28) (actual time=0.018..2.742 rows=4,116 loops=1)

11. 0.286 2.273 ↓ 3.0 4,116 1

Append (cost=0.00..251.90 rows=1,372 width=28) (actual time=0.017..2.273 rows=4,116 loops=1)

12. 1.853 1.853 ↓ 3.0 3,849 1

Seq Scan on hotel_premium_partner_actions (cost=0.00..207.83 rows=1,283 width=28) (actual time=0.016..1.853 rows=3,849 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)))
13. 0.134 0.134 ↓ 3.0 267 1

Seq Scan on hotel_premium_partner_requests (cost=0.00..16.63 rows=89 width=28) (actual time=0.010..0.134 rows=267 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)))
14. 7.688 7.688 ↑ 1.0 1 3,844

Index Scan using hotels_pkey on hotels h (cost=0.42..8.47 rows=1 width=45) (actual time=0.002..0.002 rows=1 loops=3,844)

  • Index Cond: ("hotelID" = pp_states."hotelId")
15. 15.694 29.015 ↓ 1.0 75,191 1

Hash (cost=4,366.28..4,366.28 rows=75,182 width=13) (actual time=29.015..29.015 rows=75,191 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 4554kB
16. 13.321 13.321 ↓ 1.0 75,191 1

Seq Scan on accounts acc (cost=0.00..4,366.28 rows=75,182 width=13) (actual time=0.010..13.321 rows=75,191 loops=1)

17. 0.008 0.025 ↑ 226.7 6 1

Hash (cost=64.40..64.40 rows=1,360 width=28) (actual time=0.025..0.025 rows=6 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
18. 0.017 0.017 ↑ 226.7 6 1

Seq Scan on silver_gold_data sgd (cost=0.00..64.40 rows=1,360 width=28) (actual time=0.016..0.017 rows=6 loops=1)