Database Administration
postgresql index optimization index-tuning
Updated Tue, 03 May 2022 03:13:03 GMT

How to index WHERE (start_date >= '2013-12-15')


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.

Solution

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.

Comments (3)

  • +3 – Note that partitioning does not help much with performance as soon as the query uses an index anyway. — Apr 02, 2014 at 00:36  
  • +0 – @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. — Feb 08, 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. — Feb 09, 2021 at 02:18  

External Links

External links referenced by this document:

References