| From: | Kevin Grittner <kgrittn(at)ymail(dot)com> |
|---|---|
| To: | Michael Kolomeitsev <mkolomeitsev(at)gmail(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
| Subject: | Re: Pg makes nonoptimal choice between two multicolumn indexes with the same columns but in different order. |
| Date: | 2013-12-28 21:03:28 |
| Message-ID: | 1388264608.60549.YahooMailNeo@web122306.mail.ne1.yahoo.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
Michael Kolomeitsev <mkolomeitsev(at)gmail(dot)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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Gavin Flower | 2013-12-28 21:19:20 | Re: Pg makes nonoptimal choice between two multicolumn indexes with the same columns but in different order. |
| Previous Message | Andrew Dunstan | 2013-12-28 13:54:03 | Re: Are there some additional postgres tuning to improve performance in multi tenant system |