DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=ACSi6YgqvPHhMBHT1F0IJhVnH3wSodoQFZu9am09OL9NR0vRt8ytC0WRFkHiGd5Vdr
	etfeXhju0+qFyc9cEydKUEnIrbjvRYks1uN2TLlni1NJoYUUvtV7WNcobIhdDuTtmLBe
	zgcR2vrBRXIpn394aKVDjvBk5Dp8VaG2bJBYs=
MIME-Version: 1.0
In-Reply-To: <AANLkTikjpZojDysWA5au+2pYPJeag4toJjh62YncwMog@mail.gmail.com>
References: <AANLkTikjpZojDysWA5au+2pYPJeag4toJjh62YncwMog@mail.gmail.com>
Date: Mon, 18 Apr 2011 12:24:03 -0400
Message-ID: <BANLkTim+L++hVHwvYRLPkuHsHptvBWKLow@mail.gmail.com>
Subject: Re: Custom operator class costs
From: Robert Haas <robertmhaas@gmail.com>
To: Ben Beecher <benbeecher@gmail.com>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Wed, Mar 16, 2011 at 10:10 AM, Ben Beecher <benbeecher@gmail.com> wrote:
> Hey!
> I'm having some trouble optimizing a query that uses a custom operator cl=
ass.
> #Postgres has given me a solution for natural sort -
> http://www.rhodiumtoad.org.uk/junk/naturalsort.sql
>
> I'm trying to run it over a huge table - when running it on demand,
> the data needs to be dumped to memory and sorted.
>
> Sort =A0(cost=3D31299.83..31668.83 rows=3D369 width=3D31)
> =A0Sort Key: name
> =A0-> =A0Seq Scan on solutions_textbookpage =A0(cost=3D0.00..25006.55
> rows=3D369 width=3D31)
> =A0 =A0 =A0 =A0Filter: (active AND (textbook_id =3D 263))
>
> That's obviously too slow. I've created an index using the custom
> operator class, so I don't have to do the sort every time I try to
> sort.
>
> =A0Index Scan Backward using natural_page_name_textbook on
> solutions_textbookpage =A0(cost=3D0.00..650.56 rows=3D371 width=3D31) (ac=
tual
> time=3D0.061..0.962 rows=3D369 loops=3D1)
> =A0 Index Cond: (textbook_id =3D 263)
> =A0 Filter: active
>
> Obviously a little faster!

Not totally obvious, since the sort output doesn't show how long it
actually took.

> The problem I'm having is that because operator classes have a low
> cost estimation pg missestimates and tries to do the sort on demand
> rather than using the index.
>
> I can get pg to use the index by either jacking up cpu_operator_cost
> or lowering random_page_cost. Is this the best way to do that, or is
> there a smarter way to ensure that pg uses this index when I need it.

It's pretty often necessary to lower random_page_cost, and sometimes
seq_page_cost, too.  If, for example, the database is fully cached,
you might try 0.1/0.1 rather than the default 4/1.  Raising the cpu_*
costs is equivalent, but I think it's easier to keep in your head if
you think about 1 as the nominal cost of reading a page sequentially
from disk, and then lower the value you actually assign to reflect the
fact that you'll normally be reading from the OS cache or perhaps even
hitting shared_buffers.

You might also need to tune effective_cache_size.

Is your operator class function unusually expensive?  Are you having
trouble with PG not using other indexes it should be picking up, or
just your custom one?

--=20
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company