On 13-04-13 04:54 PM, Jeff Janes wrote:
On Sat, Apr 13, 2013 at 9:14 AM, Steve Singer <ssinger@xxxxxxxxxxxxxxx
<mailto:ssinger@xxxxxxxxxxxxxxx>> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
