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