Does index work when query like 'where created_at > ?'

Question

I am using Postgresql, and need to make query like 'WHERE created_at > ?'. I am not sure if the index works in such query.

I have done an experiment. After adding an index on the created_at column, I explained the following 2 queries.

1)

EXPLAIN SELECT * FROM categories WHERE created_at > '2014-05-03 21:34:27.427505';

The result is

QUERY PLAN
------------------------------------------------------------------------------------
 Seq Scan on categories  (cost=0.00..11.75 rows=47 width=528)
   Filter: (created_at > '2014-05-03 21:34:27.427505'::timestamp without time zone)

2)

EXPLAIN SELECT * FROM categories WHERE created_at = '2014-05-03 21:34:27.427505';

The result is

                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Index Scan using index_categories_on_created_at on categories  (cost=0.14..8.16 rows=1 width=528)
   Index Cond: (created_at = '2014-05-03 21:34:27.427505'::timestamp without time zone)

Note that the first one is using 'Filter' while the second one is using 'Index Cond', according to the doc of Postgresql, the former is just a one-by-one scan while the later is using index.

Is it indicating that query like 'created_at > ?' will not be fastened by adding an index on 'created_at' column?

Update

I am using Rails 4.0, and according to the console, the index is created by

CREATE  INDEX  "index_categories_on_created_at" ON "categories"  ("created_at")

Everything seems to be right, so the only thing I can think of is the selectivity of the data in the table. See [this question](http://stackoverflow.com/questions/5203755/why-does-postgresql-perform-sequential-scan-on-indexed-column). — univerio, May 03 '14 at 22:11
Note that it's usually better to use an inclusive lower-bound (`>=`), rather than an exclusive one. Save the exclusive for the upper-bound, when dealing with all continuous range-types. — Clockwork-Muse, May 03 '14 at 23:51
If Postgres picks bad query plans, [this related answer might help.](http://stackoverflow.com/questions/8228326/how-can-i-avoid-postgresql-sometimes-choosing-a-bad-query-plan-for-one-of-two-ne/8229000#8229000) — Erwin Brandstetter, May 12 '14 at 14:13

score 2 · Accepted Answer · answered May 03 '14 at 23:17

Indexes on timestamps are normally responsive to range queries, that is, >, <, between, <=, etc. However, as univero points out, selectivity and cost estimation plays a strong role.

PostgreSQL is only going to use an index if it thinks using the index is going to be faster than not using it (for that matter, it tries to pick the fastest index to use if several are available). How much of the table are those 47 rows it expects to get back from the > query? If the answer is "10% of the table" then Postgres is not going to bother with the index. For that matter, the query planner rarely uses indexes for scans of really small tables, because if your whole table fits on 3 data pages, it's faster to scan the entire table.

You can easily play with this if you want.

1) Use EXPLAIN ANALYZE instead of just EXPLAIN so you can compare what the query planner expected vs. what it actually got.

2) Turn off and on index and table scanning with any of these statements:

SET enable_seqscan = false; --turns off table scans
SET enable_indexscan = false; -- turns of index scans
SET enable_bitmapscan = false; -- turns off bitmap index scans

If you play around, you can see where using an index is actually slower.

score 1 · Answer 2 · answered May 04 '14 at 08:35

Using an index means reading the index plus reading the selected rows from the table. There is a trade-off in that it can be more efficient simply to read only the table. The algorithms used by a DBMS to choose which is better for any given query are usually pretty good (though not perfect).

It's easily possible (and likely) that not using the index is the better choice for this query.

Using the @Clockwork-Muse AND @univerio suggestion for selectivity is generally a good idea, though it might not matter in this case due to table size. You might also use an ORDER BY created_at to see if it affects the plan.

Experimentation (per @FuzzyChef) can help find trade-off points. Use different table sizes and change other variables to see results.

Does index work when query like 'where created_at > ?'

2 Answers2