Postgresql indexing a range type

Question

I have an table which has an daterange column. I want to select rows that contain date of today.

select * from mytable where drange @> now()::date

the @> operator is true if range contains element

indexing of range types is made with the GiST index

so I created an index CREATE INDEX my_idx ON mytable USING gist (drange)

but explain select * from mytable where drange @> now()::date always returns

Seq Scan on mytable  (cost=0.00..1051.82 rows=26104 width=38)
   Filter: (drange @> (now())::date)

Laurenz Albe · Accepted Answer · 2016-12-06 15:51:19Z

3

The index is good; probably the condition is not selective enough for PostgreSQL to choose the index.

You can lower the parameter random_page_cost to make PostgreSQL decide more in favor of an index scan.

I'd suggest that you perform a couple of test runs with EXPLAIN (ANALYZE) with a sequential schan as it is now and then a few with random_page_cost set to a lower value (1, 1.5 or 2) to make PostgreSQL choose an index scan or a bitmap index scan. Then you'll see which option is the fastest – it might be the sequential scan after all.

Beware that running a query several times will cause the data blocks to be cached, which may lead to unrealistically good results. You should compare performance on a “cold” cache, which you can simulate by restarting the PostgreSQL server (and/or emptying the operating system's file system cache).

answered Dec 6, 2016 at 15:51

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql indexing a range type

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related