1

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

  • 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
1

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.

| improve this answer | |
  • 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.