Index in two columns range comparisson within the same table

Question

Is it possible in postgres or Mysql to create an index for helping a range comparison between two columns within the same table?

For example, I have a table named Test with 3 columns: id, col1 and col2, and it has 20 million rows. The query looks like this:

SELECT id,col1,col2 from Test where col1 < col2;

The query planner is saying it's using a sequential scan in the whole table. Would an index like this work?:

CREATE INDEX idx_test on Test(col1,col2);

I already created it but query planner still does sequential scans.

Is there a way to enhance that query to reduce the execution time?

Answer 1

Yes. Use math, Luis!

Rephrase the query so that all columns appear on one side of the filter (I did basically do an equivalent transformation -col2):

SELECT id,col1,col2 from Test where col1 - col2 < 0;

Add index on that expression:

CREATE INDEX idx_test on Test ( (col1-col2) );

The index will only work in PostgreSQL, but not MySQL. Depending on your overall system load, another indexing stragety might still be better. This index will not be useable for queries on col1 or col2 only.

References:

• http://use-the-index-luke.com/sql/where-clause/obfuscation/math