--btree_gist — scalar types with GiST behavior 
--btree_gist adds GiST operator classes with B-tree-like behavior for many scalar types. PostgreSQL explicitly says they usually do not beat normal B-tree, but they are useful for multicolumn GiST and nearest-neighbor on some scalars.

--Price + availability range, one GiST index
CREATE EXTENSION IF NOT EXISTS btree_gist;

DROP TABLE IF EXISTS hotel_offers;
CREATE TABLE hotel_offers
(
    offer_id      bigserial PRIMARY KEY,
    hotel_id      int NOT NULL,
    room_type     text NOT NULL,
    price_amt     numeric(12,2) NOT NULL,
    available_on  daterange NOT NULL
);

INSERT INTO hotel_offers (hotel_id, room_type, price_amt, available_on)
SELECT
    1 + (g % 300),
    CASE g % 3
        WHEN 0 THEN 'standard'
        WHEN 1 THEN 'deluxe'
        ELSE 'suite'
    END,
    round((80 + random() * 500)::numeric, 2),
    daterange(
        date '2026-01-01' + (g % 100),
        date '2026-01-01' + (g % 100) + (1 + (g % 10)),
        '[)'
    )
FROM generate_series(1, 250000) g;

CREATE INDEX ix_hotel_offers_gist
    ON hotel_offers
 USING gist (hotel_id, available_on, price_amt);
--Same hotel, overlapping dates, nearest price to target
SELECT offer_id, hotel_id, room_type, price_amt, available_on
FROM hotel_offers
WHERE hotel_id = 44
  AND available_on && daterange('2026-03-10', '2026-03-14', '[)')
ORDER BY price_amt <-> 200
LIMIT 10;
----------------------------------
----------------------------------
--cube — multidimensional similarity / vector-like coordinates
--The cube extension supports multidimensional cube values and GiST indexing, including nearest-neighbor operators.

CREATE EXTENSION IF NOT EXISTS cube;

DROP TABLE IF EXISTS customer_embeddings;
CREATE TABLE customer_embeddings
(
    customer_id   bigserial PRIMARY KEY,
    segment_name  text NOT NULL,
    vec           cube NOT NULL
);

INSERT INTO customer_embeddings (segment_name, vec)
SELECT
    CASE g % 4
        WHEN 0 THEN 'retail'
        WHEN 1 THEN 'vip'
        WHEN 2 THEN 'corporate'
        ELSE 'youth'
    END,
    cube(ARRAY[
        round((random() * 10)::numeric, 4),
        round((random() * 10)::numeric, 4),
        round((random() * 10)::numeric, 4),
        round((random() * 10)::numeric, 4)
    ])
FROM generate_series(1, 100000) g;

CREATE INDEX ix_customer_embeddings_gist
    ON customer_embeddings
 USING gist (vec);
--Nearest customers to target vector
SELECT customer_id, segment_name, vec <-> cube(ARRAY[3.1, 5.5, 1.2, 8.8]) AS dist
FROM customer_embeddings
ORDER BY vec <-> cube(ARRAY[3.1, 5.5, 1.2, 8.8])
LIMIT 20;