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.
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.
External links referenced by this document: