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

From: Matthew Nuzum <mattnuzum(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: speed up query with max() and odd estimates
Date: 2005-04-26 21:02:12
Message-ID: f3c0b408050426140240a246cb@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Simon Riggs 2005-04-26 21:02:31 Re: [HACKERS] Bad n_distinct estimation; hacks suggested?
Previous Message Steinar H. Gunderson 2005-04-26 20:48:53 Re: speed up query with max() and odd estimates