From: Jeff Janes <jeff.janes@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: sushant354@gmail.com, Tomas Vondra <tv@fuzzy.cz>
Cc: pgsql-performance@postgresql.org
Subject: Re: sequential scan unduly favored over text search gin index
Date: Wed, 29 Jun 2011 20:59:25 -0700
Message-ID: <BANLkTimEU2=yzTF9WwUsPR2frjF1PObrcA@mail.gmail.com> (raw)
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>
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 = 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. You 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
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: jeff.janes@gmail.com, tgl@sss.pgh.pa.us, tv@fuzzy.cz
Subject: Re: sequential scan unduly favored over text search gin index
In-Reply-To: <BANLkTimEU2=yzTF9WwUsPR2frjF1PObrcA@mail.gmail.com>
* 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