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.

2 Answers 2


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. Commented Apr 2, 2014 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. Commented Feb 8, 2021 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. Commented Feb 9, 2021 at 2:18

You must use the index in Postgresql. It uses the BRIN method.

CREATE INDEX some_date_idx_brin on items USING brin('created_at');

BTREE provides high performance, but it uses low disk space.

Create the index, then use

SELECT pg_size_pretty( pg_total_relation_size('some_date_idx_brin ') );

You'll see the memory using the amount.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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