1

I've searched around for an answer to this, but the only reference I could find was an obscure forum post that suggested that operators, including % (modulo) were "sargable" (i.e. could use the index that the operator was operating on)

However, we have some situations where using a modulo to parallelise our processes seems to not use the index that the modulo is on, even on the primary key field. This may be that the optimiser decides that a table scan is cheaper anyway, but it's difficult to determine the reason for the shunning of the index.

Does anyone have a definitive answer, or share similar experiences?

Postgres / EDB v9.3

TIA .

edit e.g. 

CREATE TABLE my_table(id bigint, othercolumn varchar(50), PRIMARY KEY(id))

...insert x million records into the table...

SELECT othercolumn from my_table where id % 1000 = 0
mrmoosehead
  • 145
  • 1
  • 8
  • 1
    If you create an index with that expression, then yes, Postgres would use that index if it improves performance. But you need to add the table definition (`create table ...`) and the query to your question so that this can be answered properly. See here: http://stackoverflow.com/tags/postgresql-performance/info –  Nov 18 '15 at 10:51
  • It's a very general question. I'll add an example. – mrmoosehead Nov 18 '15 at 14:37
  • The partial index is a possibility, except that we want the modulo 'parallelisation' to be configurable.... – mrmoosehead Nov 18 '15 at 14:40

1 Answers1

2

Indexes can be used on any column or expression that is immutable. In other words, the result of the expression has to depend solely on the values of the underlying columns, either directly or through the expression that they are used in. The modulo operator is therefore completely valid to be used in an index.

Patrick
  • 29,357
  • 6
  • 62
  • 90