4

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.

4

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
  • 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
  • @ErwinBrandstetter Is your statement still true with e.g. 100+ million rows, having a BTREE-index on some TIMESTAMPTZ instead of a DATE? That results in far more index entries because of many unique values. The index alone is ~2,5 GiB already in my case. – Thorsten Schöning Feb 8 at 11:23
  • 1
    @ThorstenSchöning: My comment is still basically true in Postgres 13 because B-tree indices scale excellently. Partitioning has gotten a lot better overall, though. Of course, if you can exclude a major portion of rows with a partial index or partitioning, the reduction in size helps storage and cache memory. The new feature "index deduplication" in Postgres 13 might also help with index size. – Erwin Brandstetter Feb 9 at 2:18

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.