I have a GIST index and a regular index, one intended for filtering, one intended for sorting, but Postgres WILL NOT use both

Question

I have a table with three columns: two can be used to filter the table, and the last can be used to sort it:

create table thing(
  id serial primary key,
  location geometry(Point, 4326) not null,
  created_at timestamptz not null default (now() at time zone 'utc'),
  priority float not null
);

I have been playing with the indexes to support the following query:

select id
from thing
where st_dwithin(location, 'SRID=4326;Point($0 $1)'::geometry, $2)
  and created_at >= now() - interval '$3 days'
order by priority
offset $4
limit $5;

Ideally this query could combine indexes on location, created_at, and priority to get optimal performance. Typically I'd imagine an index using btree (priority, location, created_at) would support such a query quite well, utilizing the index during the filter and delivering results pre-sorted. However, the st_dwithin part of the filter goes unassisted by the filter since that is not a gist index.

If I make an index using gist (priority, location, created_at) then the query plan utilizes the index during filtering but does not deliver results pre-sorted and sorts the results unassisted by the index.

At this point I thought we'd have to split up the index, but using gist(location, created_at) and using btree(priority) if $5 <= 20 the query plan uses the btree index and if $5 > 20 the query plan uses the gist index.

Does anyone have any idea why this might be, and what I could do to optimize the performance of both the filtering and the sorting?

Answer 1

I would reverse the question. What is the mechanics by which it could combine the indexes in the way you want? A bitmap can't supply an order, other than that of its inherent nature. What other method would be used to combine two indexes? And would it be faster than just sorting?

With a GiST index on all 3 columns, you could get it to use the KNN mechanism by writing the ORDER BY like this:

ORDER BY :min_priority <-> priority

Where :min_priority is a constant at or below the lowest possibly-used priority. In my hands this will work, in the sense of using the index for both filtering and ordering, perhaps once you have forced its hand (by setting enable_sort=off, for example). But it might still be slower than some other less-theoretically-satisfying method. Also, I am not convinced that the KNN code is totally bug-free.

The performance will depend very much on how selective each of your clauses is and what the actual values used for OFFSET and LIMIT are. In some cases one method would be faster, in others another one might be. Also, GiST estimation is not very robust at all, so you can't count on it to change plans when the actually faster method changes.