From: Scott Carey <scott@richrelevance.com>
To: Ogden <lists@darkstatic.com>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>,
	Greg Smith <greg@2ndquadrant.com>
Date: Fri, 24 Sep 2010 10:34:41 -0700
Subject: Re: Query much faster with enable_seqscan=0
Thread-Topic: [PERFORM] Query much faster with enable_seqscan=0
Thread-Index: ActcDqEWZbjiBhnCTMe3QQkJ2QBBdA==
Message-ID: <F99BA3A3-9D60-4943-81C8-9F11CD55E1C1@richrelevance.com>
References: <0D5B9ED0-00C3-4EEB-8336-0B2F8A884620@darkstatic.com>
	<20100921180618.GI17152@aart.is.rice.edu>
	<D707045F-C689-403A-9729-8DBCC39F53A1@darkstatic.com>
	<4C98FEA2.8030607@krogh.cc>
	<3062DF4E-7460-4487-818A-BF77F176E501@darkstatic.com>
	<1285096031.15919.68.camel@jd-desktop.unknown.charter.com>
	<4C9904A9.5080301@2ndquadrant.com>
	<D551B381-C707-43C1-A3F5-A3064F09152C@darkstatic.com>
	<47D7FDEE-BC85-4254-A994-4E8312EF0C6A@darkstatic.com>
In-Reply-To: <47D7FDEE-BC85-4254-A994-4E8312EF0C6A@darkstatic.com>
Accept-Language: en-US
Content-Language: en-US
acceptlanguage: en-US
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0


On Sep 22, 2010, at 6:36 AM, Ogden wrote:

>=20
> On Sep 21, 2010, at 2:34 PM, Ogden wrote:
>=20
>>=20
>> On Sep 21, 2010, at 2:16 PM, Greg Smith wrote:
>>=20
>>> Joshua D. Drake wrote:
>>>> PostgreSQL's defaults are based on extremely small and some would say
>>>> (non production) size databases. As a matter of course I always
>>>> recommend bringing seq_page_cost and random_page_cost more in line.
>>>>=20
>>>=20
>>> Also, they presume that not all of your data is going to be in memory, =
and the query optimizer needs to be careful about what it does and doesn't =
pull from disk.  If that's not the case, like here where there's 8GB of RAM=
 and a 7GB database, dramatic reductions to both seq_page_cost and random_p=
age_cost can make sense.  Don't be afraid to think lowering below 1.0 is go=
ing too far--something more like 0.01 for sequential and 0.02 for random ma=
y actually reflect reality here.
>>>=20
>>=20
>> I have done just that, per your recommendations and now what took 14 sec=
onds, only takes less than a second, so it was certainly these figures I me=
ssed around with. I have set:
>>=20
>> seq_page_cost =3D 0.01          =20
>> random_page_cost =3D 0.02  =20
>> cpu_tuple_cost =3D 0.01
>>=20
>> Everything seems to run faster now. I think this should be fine - I'll k=
eep an eye on things over the next few days.=20
>>=20
>> I truly appreciate everyone's help.=20
>>=20
>> Ogden
>>=20
>=20
>=20
> I spoke too soon - well I came in this morning and reran the query that w=
as speeded up yesterday by a lot after tweaking those numbers. This morning=
 the first time I ran it, it took 16 seconds whereas every subsequent run w=
as a matter of 2 seconds. I assume there is OS caching going on for those r=
esults. Is this normal or could it also be the speed of my disks which is c=
ausing a lag when I first run it (it's RAID 5 across 6 disks). Is there any=
 explanation for this or what should those settings really be? Perhaps 0.01=
 is too low?
>=20
> Thank you
>=20
> Ogden

When not cached, the plan with sequential scans will almost always be much =
faster.

When cached in memory, the ones using indexes are almost always faster.=20

The tuning parameters are essentially telling postgres the likelihood of fi=
nding things on disk instead versus in memory.   The default parameters are=
 essentially "not likely in memory, with a somewhat slow disk".


> --=20
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org=
)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance