Re: Pg makes nonoptimal choice between two multicolumn indexes with the same columns but in different order.

Michael Kolomeitsev <mkolomeitsev@gmail.com> wrote:

> it is clear for me why t1_b_a_idx is better. The question is: Is
> postgresql able to see that?

For a number of reasons I never consider a bulk load complete until
I run VACUUM FREEZE ANALYZE on the table(s) involved.  When I try
your test case without that, I get the bad index choice.  When I
then run VACUUM FREEZE ANALYZE on the database I get the good index
choice.

There may be some lesser maintenance which sets up visibility
information and provides the planner with enough data to make a
good choice, I just noticed that you were not following what I
consider to be rote good practice, tried it, and it solved the
problem.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company