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.


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 | 
    "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)


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...

  • 1
    Show the SQL, the explain output, the table and index definitions, and your PostgreSQL version. Edit your question, comment here when done. – Craig Ringer Mar 14 '14 at 3:04
  • Try to add some more data/dates and you will see your index being used. For a one row table it is much easier to read the whole table. – dezso Mar 14 '14 at 6:09

timestamp vs. date & time

If you are interested in the date part of your timestamp, it is cheaper to just cast it. This results is an actual date, while date_trunc() returns a timestamp! (You could cast that to date, 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(col AS type) in the index definition. You can still use the Postgres shorthand start::date in queries to match that:

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

You can to 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 now (4 bytes), an additional integer column (also 4 bytes) is just perfect for performance. Details in this related answer:
Is a composite index also good for queries on the first field?

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

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;

Will use the index.

The best solution depends on the sum of all your requirements.

Also, Postgres should still be using your original, less optimized index. How many rows are there in your table? With only a few, a sequential scan is faster and you won't seen an index scan.

Aside: don't use start as identifier. It's a reserved word in standard SQL.

  • Hey thanks for the info. My table only had a few rows so as you and @dezso pointed out a seq scan is performed. One question: is it possible to search on time, ignoring seconds? – Dumb E. Mar 15 '14 at 3:39
  • @DumbE.: search on time: sure. I suggest you open a new question with your exact requirements so you get the best solution. You can always link to this one for context. – Erwin Brandstetter Mar 15 '14 at 10:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.