Optimize query performance for a filter on col_a > col_b

Question

I have a large table in a PostgreSQL database (~100 columns, millions of rows). My query has no joins but it does have several where clauses. All of the where clauses are covered by appropriate indices except for one which has the form WHERE table.col_a > table.col_b. My question is, if I make an index on table(col_a, col_b) will that optimize the greater than operation or do I need to do something particular to make this efficient?

---

EDIT: Adding anonymized query / explain / ddl per @frank-heikens comment.

I did notice in reviewing that col_f doesn't have an index and I suspect adding it would solve a large part of my problem. That said, I'd still like to know the best way to optimize for table.col_b < table.col_a.

If it helps, col_b is almost always 0.0 and col_a is always positive. My guess would be that this reduces the performance benefit because relatively few rows are filtered by this clause. Any advice on the relative cost/benefit of a multi-column index or expression index would be welcome.

Query:

explain(analyze, verbose, buffers, settings)
SELECT id, col_a, col_b, col_c, col_d, col_e
FROM  public.table
WHERE col_f = 'VALUE' AND
      col_g != 'EXCLUDED_VALUE' AND
      col_b < col_a AND
      NOT ((col_h 'EXCLUDED_VALUE_1', 'EXCLUDED_VALUE_2') OR
           col_h IS NULL)) AND
      col_e IS NOT NULL
ORDER BY col_e DESC
LIMIT 5;

                                                     QUERY PLAN                                                                                                                                                                                              
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=10.61..10.62 rows=1 width=73) (actual time=0.101..0.102 rows=1 loops=1)
   Output: id, col_a, col_b, col_c, col_d, col_e
   Buffers: shared hit=2
   ->  Sort  (cost=10.61..10.62 rows=1 width=73) (actual time=0.100..0.100 rows=1 loops=1)
         Output: id, col_a, col_b, col_c, col_d, col_e
         Sort Key: table.col_e DESC
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=2
         ->  Seq Scan on public.table  (cost=0.00..10.60 rows=1 width=73) (actual time=0.046..0.083 rows=1 loops=1)
               Output: id, col_a, col_b, col_c, col_d, col_e
               Filter: ((table.col_h IS NOT NULL) AND (table.col_e IS NOT NULL) AND ((table.col_g)::text <> 'EXCLUDED_VALUE'::text) AND (table.col_b < table.col_a) AND ((table.col_h)::text <> ALL ('{EXCLUDED_VALUE_1,EXCLUDED_VALUE_2}'::text[])) AND ((table.col_f)::text = 'VALUE'::text))
               Rows Removed by Filter: 10
               Buffers: shared hit=2
 Planning Time: 0.467 ms
 Execution Time: 0.149 ms
(15 rows)

DDL:

-- Dumped from database version 14.10 (Homebrew)

CREATE TABLE public.table (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    col_a double precision,
    col_b double precision,
    col_c boolean,
    col_d character varying,
    col_e timestamp(6) without time zone,
    col_f character varying,
    col_g character varying,
    col_h character varying,
    -- columns not referenced in the query have been elided
);

ALTER TABLE ONLY public.table
    ADD CONSTRAINT table_pkey PRIMARY KEY (id);

CREATE INDEX index_table_on_col_e ON public.table USING btree (col_e);
CREATE INDEX index_table_on_col_g ON public.table USING btree (col_g);
CREATE INDEX index_table_on_col_h ON public.table USING btree (col_h);

-- indexes for columns not referenced in the query have been elided

Answer 1

If it helps, col_b is almost always 0.0 and col_a is always positive. My guess would be that this reduces the performance benefit because relatively few rows are filtered by this clause.

In this case, index support will not help queries with WHERE col_a > col_b at all. A partial index like has been suggested is the best option for a selective filter like that. But almost all rows pass your filter, that partial index would do more harm than good. Don't create a partial index or Postgres might use it.

CREATE INDEX ... WHERE cola > colb;

Postgres should not use it for your query, but still might based on misleading estimates and unhelpful column statistics. And other types of indexes will not help at all. The manual about extended statistics:

Regular statistics, because of their per-individual-column nature, cannot capture any knowledge about cross-column correlation.

Sadly, extended statistics for functional dependencies cannot currently help either. The manual on limitations of functional dependencies:

Functional dependencies are currently only applied when considering simple equality conditions that compare columns to constant values, and IN clauses with constant values.

So this is a dead end.

Solution

Unknown.

Certainly don't add an index for WHERE cola > colb. Indexes are for selective criteria.

There are probably other ways to optimize. But it's impossible to tell from your explain plan on a dummy table with barely any rows. We would need to know what's actually selective. Provide information as instructed here:

• Asking query performance questions

Start with a valid query. The one you show has a syntax error. Probably missing IN.

Answer 2

No, but you can add a WHERE clause to the index:

CREATE INDEX ... WHERE cola > colb;