Re: speed up query with max() and odd estimates

On 4/26/05, Steinar H. Gunderson <sgunderson(at)bigfoot(dot)com> wrote:
> On Tue, Apr 26, 2005 at 03:16:57PM -0500, Matthew Nuzum wrote:
> > Seq Scan on usage_access (cost=0.00..1183396.40 rows=12713851
> > width=116) (actual time=481796.22..481839.43 rows=3343 loops=1)
>
> That's a gross misestimation -- four orders of magnitude off!
>
> Have you considering doing this in two steps, first getting out whatever
> comes from the subquery and then doing the query? Have you ANALYZEd recently?
> Do you have an index on atime?
>

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)

--
Matthew Nuzum
www.bearfruit.org