From: Alvaro Herrera <alvherre@commandprompt.com>
To: Peter Eisentraut <peter_e@gmx.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Apparently useless bitmap scans
Date: Wed, 9 May 2007 10:29:14 -0400
Message-ID: <20070509142914.GD4504@alvh.no-ip.org> (raw)
In-Reply-To: <200705091410.57312.peter_e@gmx.net>
References: <200705091410.57312.peter_e@gmx.net>
Peter Eisentraut wrote:
> There's another odd thing about this plan from yesterday.
Is this still 8.2.1? The logic to choose bitmap indexes was rewritten
just before 8.2.4,
2007-04-17 16:03 tgl
* src/backend/optimizer/path/indxpath.c:
Rewrite choose_bitmap_and() to make it more robust in the presence of
competing alternatives for indexes to use in a bitmap scan. The former
coding took estimated selectivity as an overriding factor, causing it to
sometimes choose indexes that were much slower to scan than ones with a
slightly worse selectivity. It was also too narrow-minded about which
combinations of indexes to consider ANDing. The rewrite makes it pay more
attention to index scan cost than selectivity; this seems sane since it's
impossible to have very bad selectivity with low cost, whereas the reverse
isn't true. Also, we now consider each index alone, as well as adding
each index to an AND-group led by each prior index, for a total of about
O(N^2) rather than O(N) combinations considered. This makes the results
much less dependent on the exact order in which the indexes are
considered. It's still a lot cheaper than an O(2^N) exhaustive search.
A prefilter step eliminates all but the cheapest of those indexes using
the same set of WHERE conditions, to keep the effective value of N down in
scenarios where the DBA has created lots of partially-redundant indexes.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
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: alvherre@commandprompt.com, peter_e@gmx.net
Subject: Re: Apparently useless bitmap scans
In-Reply-To: <20070509142914.GD4504@alvh.no-ip.org>
* 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