DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=uVAfJHNiXSjosT7VE2vfbrHF8sc8k7PetpdJwGMwN0XuAId34LA1z1A75kzE2LVh3PobA1RshxaNLfTYsmHe7yigkDPR8gx1WH4vOM2YsqPHOvyENnA7VwL4P2sDBhx9yTFdIt6yUQsvxgodvkAm4BmJMo5pYSKlpJRMojp2C0g=
Message-ID: <f3c0b408050426140240a246cb@mail.gmail.com>
Date: Tue, 26 Apr 2005 16:02:12 -0500
From: Matthew Nuzum <mattnuzum@gmail.com>
Reply-To: newz@bearfruit.org
To: pgsql-performance@postgresql.org
Subject: Re: speed up query with max() and odd estimates
In-Reply-To: <20050426204853.GA29424@uio.no>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <f3c0b40805042613167c47c7e9@mail.gmail.com>
	<20050426204853.GA29424@uio.no>

On 4/26/05, Steinar H. Gunderson <sgunderson@bigfoot.com> wrote:
> On Tue, Apr 26, 2005 at 03:16:57PM -0500, Matthew Nuzum wrote:
> >  Seq Scan on usage_access  (cost=3D0.00..1183396.40 rows=3D12713851
> > width=3D116) (actual time=3D481796.22..481839.43 rows=3D3343 loops=3D1)
>=20
> That's a gross misestimation -- four orders of magnitude off!
>=20
> Have you considering doing this in two steps, first getting out whatever
> comes from the subquery and then doing the query? Have you ANALYZEd recen=
tly?
> Do you have an index on atime?
>=20

Yes, there is an index on atime. I'll re-analyze but I'm pretty
certain that runs nightly.

Regarding two steps, are you suggesting:
begin;
select * into temp_table...;
select * from temp_table...;
drop temp_table;
rollback;

I have not tried that but will.

BTW, I created an index on clients just for the heck of it and there
was no improvement. (actually, a slight degradation)

--=20
Matthew Nuzum
www.bearfruit.org