CODEWITHSUNDEEP
postgresqlindexingdate-range
microo8
microo8

Reputation: 3774

Postgresql indexing a range type

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)

Upvotes: 2

Views: 1518

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246403

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).

Upvotes: 3

Related Questions