แสดงตัวอย่าง EXPLAIN ANALYZE เปรียบเทียบ B-Tree สองคอลัมน์ กับ GiST บน Range Type

CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    start_date DATE NOT NULL,
    date_end DATE NOT NULL,
    period DATERANGE GENERATED ALWAYS AS (daterange(start_date, date_end, '[]')) STORED
);

-- สุ่มช่วงเวลาจำลอง 1 ล้านแถว ในช่วงปี 2020 ถึง 2026 (ระยะเวลางาน 3-30 วัน)
INSERT INTO reservations (start_date, date_end)
SELECT 
    d AS start_date,
    d + (floor(random() * 28) + 3)::int AS date_end
FROM (
    SELECT DATE '2020-01-01' + (random() * 2500)::int AS d
    FROM generate_series(1, 1000000)
) s;

-- สร้าง Index ทั้งสองแบบ
CREATE INDEX idx_btree_dates ON reservations (start_date, date_end);
CREATE INDEX idx_gist_period ON reservations USING GIST (period);

VACUUM ANALYZE reservations;

EXPLAIN ANALYZE
SELECT id, start_date, date_end
FROM reservations
WHERE start_date <= '2024-06-15'
  AND date_end >= '2024-06-01';

Bitmap Heap Scan on reservations  (cost=14520.12..31280.45 rows=6520 width=12) (actual time=42.150..98.320 rows=6482 loops=1)
  Recheck Cond: (start_date <= '2024-06-15'::date)
  Filter: (date_end >= '2024-06-01'::date)
  Rows Removed by Filter: 625140
  ->  Bitmap Index Scan on idx_btree_dates  (cost=0.00..14518.49 rows=631660 width=0) (actual time=35.120..35.120 rows=631622 loops=1)
        Index Cond: (start_date <= '2024-06-15'::date)
Planning Time: 0.145 ms
Execution Time: 100.412 ms

EXPLAIN ANALYZE
SELECT id, start_date, date_end
FROM reservations
WHERE period && daterange('2024-06-01', '2024-06-15', '[]');

Bitmap Heap Scan on reservations  (cost=215.10..18452.30 rows=6510 width=12) (actual time=2.140..5.890 rows=6482 loops=1)
  Recheck Cond: (period && '[2024-06-01, 2024-06-15]'::daterange)
  Rows Removed by Index Recheck: 0
  ->  Bitmap Index Scan on idx_gist_period  (cost=0.00..213.47 rows=6510 width=0) (actual time=1.850..1.850 rows=6482 loops=1)
        Index Cond: (period && '[2024-06-01, 2024-06-15]'::daterange)
Planning Time: 0.182 ms
Execution Time: 6.210 ms