Postgresql – Spliting Datetime into 2 Columns for Restaurant Reservation Schema

database-designdatatypesindexindex-tuningpostgresql

Using PostgreSQL.

The primary driver of this is that while no reservation will exist without a date and time, a user can search for and monitor reservations using a time or date or both. With a single column I'm constantly going to have to break this apart and search via date_trunc.

While PostgreSQL allows one to create an index based on a function, explain shows that this is not being used for conditions using date_trunc.

Table:

development2=# \d reservations
                                        Table "public.reservations"
     Column      |            Type             |                         Modifiers                         
-----------------+-----------------------------+-----------------------------------------------------------
 id              | integer                     | not null default nextval('reservations_id_seq'::regclass)
 tops            | integer                     | not null
 price           | numeric                     | 
 state           | integer                     | not null
 restaurant_id   | integer                     | not null
 seller_id       | integer                     | 
 customer_id     | integer                     | 
 created_at      | timestamp without time zone | not null
 start           | timestamp without time zone | 
Indexes:
    "reservations_pkey" PRIMARY KEY, btree (id)
    "start_date_index" btree (date_trunc('day'::text, start))
Foreign-key constraints:
    "reservations_customer_id_fk" FOREIGN KEY (customer_id) REFERENCES users(id)
    "reservations_restaurant_id_fk" FOREIGN KEY (restaurant_id) REFERENCES restaurants(id)
    "reservations_seller_id_fk" FOREIGN KEY (seller_id) REFERENCES users(id)

Query:

development2=# explain select id,start from reservations where date_trunc('day', start) = '2014-03-14';
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Seq Scan on reservations  (cost=0.00..1.01 rows=1 width=12)
   Filter: (date_trunc('day'::text, start) = '2014-03-14 00:00:00'::timestamp without time zone)
(2 rows

)

Not even sure how to approach the time search, to_char + IMMUTABLE I guess…

Best Answer

timestamp vs. date and time

If you are interested in the date part of your timestamp, it is cheaper to just cast it. The result is an actual date, while date_trunc() returns a timestamp! (You could cast that to date in turn, but don't bother.) Your index should be:

CREATE INDEX start_date_index ON reservations (cast(start AS date));

Use the more verbose standard notation cast(column AS type) in the index definition. The Postgres shorthand would require an additional set of parentheses in this context: (column::type). You can still use either notation in queries to match:

SELECT id, start
FROM   reservations
WHERE  start::date = '2014-03-14';

See:

You can do similar things with start::time if needed.
Either way, I would certainly not split the date and time part. A timestamp is almost always the superior design.

Index tuning

Depending on your actual use cases, you could make that a multi-column index including the id:

CREATE INDEX start_id_date_index ON reservations (cast(start AS date), id);

That would serve the above query perfectly, possibly even with an index-only scan. And since you are now using an actual date in the index (4 bytes), an additional integer column (also 4 bytes) is just perfect for performance. See:

What's more, you can also just use a plain index on the timestamp column:

CREATE INDEX start_start_index ON reservations (start);

Just make sure your WHERE conditions are "sargable". Like:

SELECT id, start
FROM   reservations
WHERE  start >= '2014-03-14 0:0'::timestamp
WHERE  start <  '2014-03-15 0:0'::timestamp;

This will use the index.

The best solution depends on the sum of all your requirements. Also, Postgres can use your original, less optimal index nonetheless. How many rows are there in your table? With only few, a sequential scan is always faster and you won't see an index scan.

Related Question