Subject: Re: Query much faster with enable_seqscan=0
Mime-Version: 1.0 (Apple Message framework v1081)
Content-Type: text/plain; charset=us-ascii
From: Ogden <lists@darkstatic.com>
In-Reply-To: <D551B381-C707-43C1-A3F5-A3064F09152C@darkstatic.com>
Date: Wed, 22 Sep 2010 08:36:43 -0500
Cc: Greg Smith <greg@2ndquadrant.com>
Content-Transfer-Encoding: quoted-printable
Message-Id: <47D7FDEE-BC85-4254-A994-4E8312EF0C6A@darkstatic.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>
To: pgsql-performance@postgresql.org


On Sep 21, 2010, at 2:34 PM, Ogden wrote:

>=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_page_cost can make sense.  Don't be afraid to =
think lowering below 1.0 is going too far--something more like 0.01 for =
sequential and 0.02 for random may actually reflect reality here.
>>=20
>=20
> I have done just that, per your recommendations and now what took 14 =
seconds, only takes less than a second, so it was certainly these =
figures I messed 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 =
keep an eye on things over the next few days.=20
>=20
> I truly appreciate everyone's help.=20
>=20
> Ogden
>=20


I spoke too soon - well I came in this morning and reran the query that =
was 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 was a matter of 2 seconds. I assume there is OS caching =
going on for those results. Is this normal or could it also be the speed =
of my disks which is causing 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?

Thank you

Ogden=