DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=ymail.com;
 h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
 b=0BsteklTHYmXNzRpjnkDvPI2Iht7reeRvGCLTm4TfGyBVMFKOvOOytbbqXdCB3lxy3lMj/FcLrl0U0Q792hAJ6t3NvvgcVrWQrIDXwDED4B+qZ2SmR/rbBcF7VyQQk1sdK6oWIJo6ld51dNHtQ8O0Kb0UzNnC70ObK5H+6TMtq8=;
References: 
 <CAABbzO01zK4Ab=vd3UD1Jd++-Hju9bLerzS1+=JnpMsXCLtg8w@mail.gmail.com>
Message-ID: <1388264608.60549.YahooMailNeo@web122306.mail.ne1.yahoo.com>
Date: Sat, 28 Dec 2013 13:03:28 -0800 (PST)
From: Kevin Grittner <kgrittn@ymail.com>
Reply-To: Kevin Grittner <kgrittn@ymail.com>
Subject: Re: Pg makes nonoptimal choice between two multicolumn indexes with
 the same columns but in different order.
To: Michael Kolomeitsev <mkolomeitsev@gmail.com>,
 "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
In-Reply-To: 
 <CAABbzO01zK4Ab=vd3UD1Jd++-Hju9bLerzS1+=JnpMsXCLtg8w@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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.=A0 When I try
your test case without that, I get the bad index choice.=A0 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


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance