MIME-Version: 1.0
In-Reply-To: <4289.1308635599@sss.pgh.pa.us>
References: <1308584339.2488.12.camel@dragflick>
	<4DFF27C5020000250003E9A4@gw.wicourts.gov>
	<1308585840.2488.13.camel@dragflick> <4DFF990F.8080906@fuzzy.cz>
	<1308623134.1747.1.camel@dragflick> <4289.1308635599@sss.pgh.pa.us>
Date: Wed, 29 Jun 2011 20:59:25 -0700
Message-ID: <BANLkTimEU2=yzTF9WwUsPR2frjF1PObrcA@mail.gmail.com>
Subject: Re: sequential scan unduly favored over text search gin index
From: Jeff Janes <jeff.janes@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: sushant354@gmail.com, Tomas Vondra <tv@fuzzy.cz>,
	pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Mon, Jun 20, 2011 at 10:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Sushant Sinha <sushant354@gmail.com> writes:
>>> I guess you could poke the planner towards the bitmap scan by lowering
>>> the random_page_cost (the default value is 4, I'd say lowering it to 2
>>> should do the trick).
>
>> The numbers that I gave was after setting random_page_cost =3D 1.0 After
>> this I don't know what to do.
>
> I think part of the issue here is that the @@ operator is expensive,
> and so evaluating it once per row is expensive, but the pg_proc.procost
> setting for it doesn't adequately reflect that. =A0You could experiment
> with tweaking that setting ...

In something I was testing a couple months ago, by far the biggest
expense of the @@ operator in a full table scan was in crawling
through the entire toast table (and not necessarily in sequential
order) in order to get the tsvector data on which to apply the
operator.  So increasing the cost of @@ might very well be the best
immediate solution, but should the cost estimation code be changed to
explicitly take page reads associated with toast into account, so that
cost of @@ itself and can remain a CPU based estimate rather than an
amalgam of CPU and IO?

Cheers,

Jeff