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.

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
Bill
  • 143
  • 5

2 Answers2

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.

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
Alexandros
  • 1,012
  • 4
  • 12
  • 23
  • 3
    Note that partitioning does not help much with performance as soon as the query uses an index anyway. – Erwin Brandstetter Apr 02 '14 at 00: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 08 '21 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 09 '21 at 02:18
-1

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.

Gismat Gusein
  • 51
  • 9