CODEWITHSUNDEEP
postgresqlpostgis
wing_man
wing_man

Reputation: 153

Postgres Force Query To Recreate Execution Plan

In best of my knowledge Postgres final an execution plan for a query on its 1~5th execution and then stuck to it.

My query(for table contains billions of rows and i have to pick top n):

select col1, col2 
from table_a
where col1='a'
and col3='b'
order by col1 desc
limit 5;

There is an existing index (ix_1) on (col1, col3) that query is using. Moving up to Postgres 12 I have created an container index (ix_2) as under: (col1 desc, col3) include (col2)

Now I want query to use (ix_2) to make it an index only scan as col2 is included in ix_2 but query still use old (ix_1).

Since index forcing hints also not work in Postgres, so is there anyway in Postgres to force query to recreate its execution plan, so that query may consider my new index (ix_2)?

Upvotes: 0

Views: 1222

Answers (1)

jjanes
jjanes

Reputation: 44363

What an interesting username.

I think your assumptions about what is going on are wrong. Creating a new index sends out an invalidation message, which should force all other sessions to re-plan the query even if they think they already know the best plan.

Most likely what is going on is that the planner just re-picks the old plan anyway, because it still thinks it will be fastest. An index-only scan is only beneficial of many of the table pages are marked as allvisible. If few of them are, then there isn't much gain. But the index is probably larger, which will give it a (slightly) higher cost estimate. You should VACUUM the table to make sure the visibility map is current.

But really if you just want to get it to use the IOS, rather than do a root cause analysis, then you can just drop the old index. There is no point in having both.

Also, I wouldn't bother with INCLUDE, unless col2 is of a type that doesn't define btree operators. Just throw it into the main body of the index like (col1 desc, col3, col2).

Finally, there is no point in ordering by a column which you just forced to all have identical values to each other.

Upvotes: 1

Related Questions