explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gHiX

Settings
# exclusive inclusive rows x rows loops node
1. 0.028 1,093.692 ↓ 9.0 9 1

Nested Loop Left Join (cost=1,536.16..1,552.57 rows=1 width=924) (actual time=1,090.432..1,093.692 rows=9 loops=1)

2.          

CTE hotels

3. 1.751 1,093.451 ↓ 9.0 9 1

Unique (cost=1,535.30..1,535.31 rows=1 width=760) (actual time=1,090.374..1,093.451 rows=9 loops=1)

4. 26.291 1,091.700 ↓ 15,351.0 15,351 1

Sort (cost=1,535.30..1,535.31 rows=1 width=760) (actual time=1,090.372..1,091.700 rows=15,351 loops=1)

  • Sort Key: brewed_bedbanks_packages_2000_1909250436.ap_id, brewed_bedbanks_packages_2000_1909250436.total_price, brewed_bedbanks_packages_2000_1909250436.departure_date
  • Sort Method: quicksort Memory: 8,300kB
5. 1,065.409 1,065.409 ↓ 15,351.0 15,351 1

Index Scan using ix_brewed_bedbanks_packages_2000_price_order_1909250436 on brewed_bedbanks_packages_2000_1909250436 (cost=0.56..1,535.29 rows=1 width=760) (actual time=25.519..1,065.409 rows=15,351 loops=1)

  • Index Cond: ((departure_date >= 190,826) AND (departure_date <= 191,027) AND (stay >= 0) AND (stay <= 999) AND (total_price >= 0) AND (total_price <= 999,999))
  • Filter: ((seats >= 2) AND (available_rooms > 0) AND (segments > 1) AND (ap_id = ANY ('{P-000331872,P-000331959,P-000331993,P-000331997,P-000331999,P-000332044,P-000332045,P-000332046,P-000332047,P-000332048,P-000332060,P-000332111,P-000332112,P-000332316,P-000332318,P-000332405,P-000332411,P-000332413,P-000332414,P-000332415,P-000332416,P-000332417,P-000332418,P-000332419,P-000332420,P-000332421,P-000332467,P-000332468,P-000332469,P-000332471}'::text[])))
  • Rows Removed by Filter: 902,022
6. 0.021 1,093.628 ↓ 9.0 9 1

Nested Loop (cost=0.55..8.59 rows=1 width=858) (actual time=1,090.409..1,093.628 rows=9 loops=1)

7. 1,093.472 1,093.472 ↓ 9.0 9 1

CTE Scan on hotels ho (cost=0.00..0.02 rows=1 width=760) (actual time=1,090.376..1,093.472 rows=9 loops=1)

8. 0.135 0.135 ↑ 1.0 1 9

Index Scan using flights_all_raw_pkey1 on flights_all fa (cost=0.55..8.57 rows=1 width=164) (actual time=0.015..0.015 rows=1 loops=9)

  • Index Cond: ((flight_key)::text = ho.flight_key)
  • Filter: (seats >= 2)
9. 0.036 0.036 ↓ 0.0 0 9

Index Scan using ix_csp_apid_year_month on csp c (cost=0.29..8.66 rows=1 width=38) (actual time=0.004..0.004 rows=0 loops=9)

  • Index Cond: ((ap_id = ho.ap_id) AND (month = ("substring"((ho.departure_date)::text, 3, 2))::smallint))
  • Filter: ((year)::text = "substring"((ho.departure_date)::text, 1, 2))