From: Gavin Flower <GavinFlower@archidevsys.co.nz>
To: Kevin Grittner <kgrittn@ymail.com>
To: Michael Kolomeitsev <mkolomeitsev@gmail.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Pg makes nonoptimal choice between two multicolumn indexes with the same columns but in different order.
Date: Sun, 29 Dec 2013 10:19:20 +1300
Message-ID: <52BF4058.6000306@archidevsys.co.nz> (raw)
In-Reply-To: <1388264608.60549.YahooMailNeo@web122306.mail.ne1.yahoo.com>
References: <CAABbzO01zK4Ab=vd3UD1Jd++-Hju9bLerzS1+=JnpMsXCLtg8w@mail.gmail.com>
<1388264608.60549.YahooMailNeo@web122306.mail.ne1.yahoo.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 29/12/13 10:03, Kevin Grittner wrote:
> 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
>
>
Curious: Would it be feasible to do some kind of ANALYZE during a bulk
operation? Say if you could tell the system you expected to change 20%
of the records in advance: then you could sample some of the changes and
modify the statistics with 0.2 times that plus 0.8 of the pre-existing
statistics.
BEGIN BULK OPERATION CHANGE 20%
[... several transactions ...]
END BULK OPERATION
The sampling could be done as part of the individual operations or at
the end of the bulk operation - whichever is deemed more practicable
(possibly a bit of both?).
Cheers,
Gavin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: GavinFlower@archidevsys.co.nz, kgrittn@ymail.com, mkolomeitsev@gmail.com
Subject: Re: Pg makes nonoptimal choice between two multicolumn indexes with the same columns but in different order.
In-Reply-To: <52BF4058.6000306@archidevsys.co.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox