0

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?

1
  • A workaround I found is to use the st_x and st_y of the point in the btree index instead, but this is obviously full of limitations and only works because location is a geometry(Point). Apr 11, 2022 at 22:50

1 Answer 1

2

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.

1
  • Thank you, this helped me immensely! That is really clever use of the <-> operator (which I had never encountered prior to postgis) and worked like a charm. FWIW to the community you can use :max_priority to get desc as well (gist indexes do not support order). Apr 12, 2022 at 0:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.