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