I have a table named items, and inside the table there is a some_date column, with date as the datatype.

I was wondering how can I index the some_date column with PostgreSQL

SELECT "items".* FROM "items" WHERE (some_date >= '2013-12-15')
Bitmap Heap Scan on items  (cost=126.48..4671.78 rows=6459 width=384) (actual time=0.799..2.035 rows=7511 loops=1)
  Recheck Cond: (some_date >= '2014-03-30'::date)
  ->  Bitmap Index Scan on index_items_on_some_date_and_end_date  (cost=0.00..124.86 rows=6459 width=0) (actual time=0.744..0.744 rows=8777 loops=1)
        Index Cond: (some_date >= '2014-03-30'::date)
Total runtime: 2.439 ms

I've thought about partial index, but some_date is flexible in terms of the value used.

up vote 3 down vote accepted

You just need to add a B-tree index to the some_date DB field. Partial indexes work, only if you know your query parameters and want to exclude / include particular ranges (http://www.postgresql.org/docs/current/static/indexes-partial.html).

Another alternative is to use table partitioning (http://www.postgresql.org/docs/current/static/ddl-partitioning.html) for the some_date DB field per day or month, so you may exclude huge number of rows easily.

  • 3
    Note that partitioning does not help much with performance as soon as the query uses an index anyway. – Erwin Brandstetter Apr 2 '14 at 0:36

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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