From: Steve Singer <ssinger@ca.afilias.info>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: slow bitmap heap scans on pg 9.2
Date: Sun, 14 Apr 2013 20:06:42 -0400
Message-ID: <516B4492.7090204@ca.afilias.info> (raw)
In-Reply-To: <CAMkU=1ykX8A36=n3BO9u8aGuJtRHB+axRWoEEsorMd=f6rgN5A@mail.gmail.com>
References: <51656E03.7000701@ca.afilias.info>
<CAMkU=1xQXQWBw8AngvhM70K9GOGby_WAGTR3YTDZ26apv9k9pw@mail.gmail.com>
<5165FBA1.90704@ca.afilias.info>
<5166C69B.1010505@ca.afilias.info>
<CAMkU=1wW1=merFPN9DdNY5vfGshPFkoB8Ze=BjWr=f3Q0KYjPQ@mail.gmail.com>
<51698452.1050202@ca.afilias.info>
<CAMkU=1ykX8A36=n3BO9u8aGuJtRHB+axRWoEEsorMd=f6rgN5A@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 13-04-13 04:54 PM, Jeff Janes wrote:
> On Sat, Apr 13, 2013 at 9:14 AM, Steve Singer <ssinger@ca.afilias.info
> <mailto:ssinger@ca.afilias.info>> wrote:
>
>
> indexTotalCost += index->pages * spc_random_page_cost / 100000.0;
>
> Is driving my high costs on the inner loop. The index has 2-5
> million pages depending on the partition . If I run this against
> 9.2.2 with / 10000.0 the estimate is even higher.
>
> If I try this with this with the
>
> *indexTotalCost += log(1.0 + index->pages / 10000.0) *
> spc_random_page_cost;
>
> from 9.3 and I play I can make this work I can it pick the plan on
> some partitions with product_id=2 but not product_id=1. If I
> remove the fudge-factor cost adjustment line I get the nested-loop
> plan always.
>
>
> That was only temporarily the formula during 9.3dev. Tom re-did that
> entire part of the code rather substantially in the current tip of 9.3
> (commit 31f38f28b00cbe2b). Now it is based on the number of tuples, and
> the height, rather than pages, and is multiplied by the
> cpu_operator_cost not the random_page_cost.
>
> descentCost = ceil(log(index->tuples) / log(2.0)) * cpu_operator_cost;
>
> ...
>
> descentCost = (index->tree_height + 1) * 50.0 * cpu_operator_cost;
>
>
> Breaking the index into smaller partial indexes for each year seems
> to be giving me the plans I want with random_page_cost=2 (I might
> also try partial indexes on the month).
>
> Even with the 9.3 log based fudge-factor we are seeing the
> fudge-factor being big enough so that the planner is picking a table
> scan over the index.
>
>
> Have you tried it under 9.3 HEAD, rather than just back-porting the
> temporary
> *indexTotalCost += log(1.0 + index->pages / 10000.0) * spc_random_page_cost;
> code into 9.2?
>
> If you are trying to make your own private copy of 9.2, then removing
> the fudge factor altogether is probably the way to go. But if you want
> to help improve future versions, you probably need to test with the most
> up-to-date dev version.
I will do that in a few days. I don't have enough disk space on this
dev server to have a 9.2 datadir and a 9.3 one for this database. Once
I have a solution that I can use with 9.2 firmed up I can upgrade the
datadir to 9.3 and test this. I am hoping I can get a set of partial
indexes that will give good results with an unmodified 9.2, so far that
looks promising but I still have more cases to verify (these indexes
take a while to build).
>
> A lot of loop iterations can be satisfied by cached pages of the
> index the fudge-factor doesn't really account for this.
>
>
>
> Setting random_page_cost to 2 is already telling it that most of fetches
> are coming from the cache. Of course for the upper blocks of an index
> even more than "most" are likely to be, but the latest dev code takes
> care of that.
>
> Cheers,
>
> Jeff
--
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: ssinger@ca.afilias.info, jeff.janes@gmail.com
Subject: Re: slow bitmap heap scans on pg 9.2
In-Reply-To: <516B4492.7090204@ca.afilias.info>
* 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