I am creating a db schema for a home exchange site. Host can set his home as exposed for exchange on certain dates. Guest must be able to view available dates. If exposed date is free (not reserved by anyone), we consider it available.
The question is how to store available dates in order to efficiently find related homes.
First, I created a table exposed_days, containing home_id, particular date, reserved (boolean) and guest_id. E.g, host exposes his home from Feb 10 to Feb 20 - 10 records are created, one for each date. Then, if a guest reserves, say, from 10 to 15 - we mark these days as reserved.
home_id |date | reserved | guest_id
--------+----------+----------+-----------
1 |2020-02-10|t |10
1 |2020-02-11|t |10
....
1 |2020-02-15|t |10
1 |2020-02-16|f |NULL
1 |2020-02-17|f |NULL
....
1 |2020-02-20|f |NULL
Now we can select available days and show available homes for each day:
SELECT rd.date, ARRAY_AGG(homes.title) FROM homes
JOIN exposed_days ed ON ed.home_id = homes.id AND NOT ed.reserved
GROUP BY rd.date
ORDER BY rd.date
However, my colleague said storing days is stupid. He suggested using date ranges. I created two tables
exposed_dates:
home_id |daterange
--------+----------
1 | [2020-02-10,2020-02-20]
reserved_dates:
home_id |daterange |guest_id
--------+-----------------------+---------
1 |[2020-02-10,2020-02-15]|10
But I can’t figure out how to make an efficient request.
My best try is
SELECT h.id, generate_series(lower(daterange), upper(daterange), '1 day'::interval)
FROM exposed_dates
JOIN homes h ON exposed_dates.home_id = h.id
EXCEPT
SELECT h.id, generate_series(lower(daterange), upper(daterange), '1 day'::interval)
FROM reserved_dates
JOIN homes h ON reserved_dates.home_id = h.id
I tested the request for 10000 homes, ~10000 exposed date ranges (10 days each) and ~5000 reserved date ranges and it took about 500ms, which is too slow
explain log:
"SetOp Except (cost=3961202.85..4111337.85 rows=10009000 width=20) (actual time=383.094..454.354 rows=50045 loops=1)"
" -> Sort (cost=3961202.85..4011247.85 rows=20018000 width=20) (actual time=383.091..421.361 rows=190171 loops=1)"
" Sort Key: "*SELECT* 1".id, "*SELECT* 1".generate_series"
" Sort Method: external merge Disk: 5560kB"
" -> Append (cost=319.20..301963.97 rows=20018000 width=20) (actual time=10.119..201.710 rows=190171 loops=1)"
" -> Subquery Scan on "*SELECT* 1" (cost=319.20..150981.98 rows=10009000 width=20) (actual time=10.118..115.715 rows=120108 loops=1)"
" -> Hash Join (cost=319.20..50891.98 rows=10009000 width=16) (actual time=10.117..93.285 rows=120108 loops=1)"
" Hash Cond: (h.id = exposed_dates.home_id)"
" -> Seq Scan on homes h (cost=0.00..315.09 rows=10009 width=8) (actual time=0.011..4.523 rows=10009 loops=1)"
" -> Hash (cost=194.09..194.09 rows=10009 width=22) (actual time=9.687..9.687 rows=10009 loops=1)"
" Buckets: 16384 Batches: 1 Memory Usage: 676kB"
" -> Seq Scan on exposed_dates (cost=0.00..194.09 rows=10009 width=22) (actual time=0.014..3.981 rows=10009 loops=1)"
" -> Subquery Scan on "*SELECT* 2" (cost=319.20..150981.98 rows=10009000 width=20) (actual time=3.257..63.583 rows=70063 loops=1)"
" -> Hash Join (cost=319.20..50891.98 rows=10009000 width=16) (actual time=3.257..51.251 rows=70063 loops=1)"
" Hash Cond: (h_1.id = reserved_dates.home_id)"
" -> Seq Scan on homes h_1 (cost=0.00..315.09 rows=10009 width=8) (actual time=0.009..2.618 rows=10009 loops=1)"
" -> Hash (cost=194.09..194.09 rows=10009 width=22) (actual time=3.173..3.174 rows=10009 loops=1)"
" Buckets: 16384 Batches: 1 Memory Usage: 676kB"
" -> Seq Scan on reserved_dates (cost=0.00..194.09 rows=10009 width=22) (actual time=0.010..1.442 rows=10009 loops=1)"
"Planning time: 0.929 ms"
"Execution time: 466.946 ms"
Maybe indexes or a trickier query will help solve the problem? or is it better to stick with a 'naive' decision with exposed_days for this case?
-
(difference) operator to find the free days