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
