explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FLwS

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 138.662 ↑ 1.0 1 1

Aggregate (cost=1,460.29..1,460.30 rows=1 width=12) (actual time=138.662..138.662 rows=1 loops=1)

2.          

CTE hotels

3. 0.280 137.984 ↓ 38.0 38 1

Unique (cost=1,443.05..1,443.06 rows=1 width=1,242) (actual time=137.553..137.984 rows=38 loops=1)

4. 7.221 137.704 ↓ 2,847.0 2,847 1

Sort (cost=1,443.05..1,443.06 rows=1 width=1,242) (actual time=137.553..137.704 rows=2,847 loops=1)

  • Sort Key: brewed_charter_packages_2000_1910030936.ap_id, brewed_charter_packages_2000_1910030936.price_current, brewed_charter_packages_2000_1910030936.departure_date
  • Sort Method: quicksort Memory: 2987kB
5. 130.483 130.483 ↓ 2,847.0 2,847 1

Index Scan using ix_brewed_charter_packages_2000_price_order on brewed_charter_packages_2000_1910030936 (cost=0.56..1,443.04 rows=1 width=1,242) (actual time=1.034..130.483 rows=2,847 loops=1)

  • Index Cond: ((departure_date >= 190919) AND (departure_date <= 191017) AND (stay >= 3) AND (stay <= 15) AND (price_current > 0) AND (price_current >= 0) AND (price_current <= 999999))
  • Filter: ((seats >= 2) AND (available_rooms > 0) AND ((min_child_age IS NULL) OR (min_child_age <= 30)) AND (ap_id = ANY ('{P-00000002,P-00000005,P-00000006,P-00000015,P-00000029,P-000000296,P-000000318,P-000000416,P-00000045,P-00000046,P-000000480,P-000000500,P-000000553,P-000000555,P-000000584,P-000000585,P-000000586,P-00000060,P-000000608,P-00000064,P-000000647,P-00000079,P-00000098,P-00000116,P-00000151,P-00000153,P-000001574,P-00000160,P-00000162,P-00000163,P-00000171,P-00000187,P-00000194,P-00000197,P-00000201,P-00000203,P-00000205,P-00000220,P-00000221,P-00000225,P-00000254,P-00000279,P-000002845,P-000003748,P-000003749,P-000003750,P-000007975,P-000007977,P-000008082,P-000008097,P-000008217,P-000008243,P-000008307,P-000008335,P-000008364,P-000008385,P-000008388,P-000008402,P-000008430,P-000008432,P-000008443,P-000008445,P-000008451,P-000008481,P-000008891,P-000008913,P-000008928,P-000008932,P-000008934,P-000008938,P-000008941,P-000009034,P-000009128,P-000009280,P-000009283,P-000009323,P-000009324,P-000009367,P-000009368,P-000009399,P-000011133,P-000011179,P-000011522,P-000011525,P-000012584,P-000012586,P-000012697,P-000012759,P-000019975,P-000020447,P-000022047,P-000048944,P-000051316,P-000051318,P-000051319,P-000053749,P-000054196,P-000063388,P-000070722,P-000070748}'::text[])))
  • Rows Removed by Filter: 50375
6. 0.040 138.654 ↓ 38.0 38 1

Nested Loop Left Join (cost=0.84..17.22 rows=1 width=4) (actual time=137.597..138.654 rows=38 loops=1)

7. 0.023 138.386 ↓ 38.0 38 1

Nested Loop (cost=0.55..8.59 rows=1 width=40) (actual time=137.582..138.386 rows=38 loops=1)

8. 138.021 138.021 ↓ 38.0 38 1

CTE Scan on hotels ho (cost=0.00..0.02 rows=1 width=72) (actual time=137.555..138.021 rows=38 loops=1)

9. 0.342 0.342 ↑ 1.0 1 38

Index Scan using flights_all_raw_pkey on flights_all fa (cost=0.55..8.57 rows=1 width=67) (actual time=0.009..0.009 rows=1 loops=38)

  • Index Cond: ((flight_key)::text = ho.flight_key)
  • Filter: (seats >= 2)
10. 0.228 0.228 ↑ 1.0 1 38

Index Only Scan using ix_csp_apid_year_month on csp c (cost=0.29..8.62 rows=1 width=36) (actual time=0.006..0.006 rows=1 loops=38)

  • 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))
  • Heap Fetches: 30
Planning time : 0.518 ms