Обсуждение: Multiple multi-column gist indexes that shares one geometry field

Поиск
Список
Период
Сортировка

Multiple multi-column gist indexes that shares one geometry field

От
王景隆
Дата:
Hi,

I am using Postgres 11 with postgis.

I am maintaining a postgres table containing real-world objects. The table has three columns:
  • "polygon" of type geometry, which indicates the object's location.
  • "create_time" of type timezone
  • "state" of type string, can be one of "VALID", "INVALID", "REMOVED"
And I create two multi-column indexes:
  • gist(state, polygon), in order to find all valid/invalid objects within a region.
  • gist(create_time, polygon), in order to find all objects created after s specified date within a region.
since I would like to support two kinds of queries.

However, recently I find that Postgres always uses index "gist(create_time, polygon)" no matter what the query is. More specifically, even if I specify "state == VALID and ST_DWithin(..., polygon, 3)" in the WHERE clause, postgres still uses index "gist(create_time, polygon)" rather than gist(state, polygon), which is not a optimal query plan and makes the query time much longer.

Only after I remove "gist(create_time, polygon)", postges uses the other index, and the query time becomes acceptable.

I have tried to run "ANALYSE table" command, but after that postgres would still use the non-optimal index.

So is there any solution that can make postgres always picks the correct index? I have read the documentation and searched all previous posts, but I did not find a solution on this. Thanks!


Best regards
JInglong Wang