Message-ID: <4D8102CC.5060309@peak6.com>
Date: Wed, 16 Mar 2011 13:34:52 -0500
From: Shaun Thomas <sthomas@peak6.com>
Reply-To: sthomas@peak6.com
Organization: Peak6
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.7) Gecko/20100720 Lightning/1.0b2 Thunderbird/3.1.1
MIME-Version: 1.0
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
CC: "tgl@sss.pgh.pa.us" <tgl@sss.pgh.pa.us>, "tgarnett@panjiva.com"
	<tgarnett@panjiva.com>, "pgsql-performance@postgresql.org"
	<pgsql-performance@postgresql.org>
Subject: Re: Re: Adding additional index causes 20,000x slowdown
	for certain select queries - postgres 9.0.3
References: <4D80B076020000250003B9B1@gw.wicourts.gov>
	<4D80B0AF020000250003B9B4@gw.wicourts.gov>
	<4D80B0AF020000250003B9B4@gw.wicourts.gov>
In-Reply-To: <4D80B0AF020000250003B9B4@gw.wicourts.gov>
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
content-transfer-encoding: 7bit

On 03/16/2011 12:44 PM, Kevin Grittner wrote:

> Well, that's one way of looking at it.  Another would be that the
> slower plan with the backward scan was only estimated to be 14.5%
> less expensive than the fast plan, so a pretty moderate modifier
> would have avoided this particular problem.

I was wondering about that myself. Considering any backwards scan would 
necessarily be 10-100x slower than a forward scan unless the data was on 
an SSD, I assumed the planner was already using a multiplier to 
discourage its use.

If not, it seems like a valid configurable. We set our random_page_cost 
to 1.5 once the DB was backed by NVRAM. I could see that somehow 
influencing precedence of a backwards index scan. But even then, SSDs 
and their ilk react more like RAM than even a large RAID... so should 
there be a setting that passes such useful info to the planner?

Maybe a good attribute to associate with the tablespace, if nothing else.

-- 
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@peak6.com

______________________________________________

See  http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email