sql - Index in two columns range comparisson within the same table -

Is it possible in a postgrace or a mux to help compare the range between the two columns in the same table ? id , col1 and col2 test . >, And there are 20 million rows in the query that looks like this:

  SELECT id, col1, col2 test, where col1 & lt; Col2;  

The query planner is saying that he is using a sequential scan in the entire table. Will this work be an index ?:

  create index idx_test on test (col1, col2);  

I have already made it but the query planner still scans sequentially.

Is there a way to increase that query to minimize execution time?

Yes use math, louis!

Repeat the query so that all the columns appear on one side of the filter (I originally made a similar change -col2 ):

  SELECT id, col1, col2 test from where col1 - col2 & lt; 0;  

Add an index to that expression:

  create idx_test on test (col1-col2);  

The index will work only in PostgreSQL, but MySQL is not based on your entire system load, another indexing struggy can still be better. This index will not be usable for questions on the col1 or col2 .

Reference: