Skip site navigation (1) Skip section navigation (2)

Re: bad estimates

From: Ken Geis <kgeis(at)speakeasy(dot)org>
To: Bruno Wolff III <bruno(at)wolff(dot)to>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: bad estimates
Date: 2003-08-30 05:05:18
Message-ID: 3F50308E.6070100@speakeasy.org (view raw or flat)
Thread:
Lists: pgsql-performance
Bruno Wolff III wrote:
> I haven't come up with any great ideas for this one. It might be interesting
> to compare the explain analyze output from the distinct on query with
> and without seqscans enabled.

Can't do that comparison.  Remember, with seqscan it fails.  (Oh, and 
that nested loops solution I thought was fast actually took 31 minutes 
versus 29 for index scan in 7.4b2.)

I ran another query across the same data:

select price_date, count(*) from day_ends group by price_date;

It used a table scan and hashed aggregates, and it ran in 5.5 minutes. 
Considering that, pgsql should be able to do the query that I had been 
running in a little more time than that.  So...

 From what I've learned, we want to convince the optimizer to use a 
table scan; that's a good thing.  I want it to use hashed aggregates, 
but I can't convince it to (unless maybe I removed all of the 
statistics.)  To use group aggregates, it first sorts the results of the 
table scan (all 17 million rows!)  There ought to be some way to tell 
pgsql not to do sorts above a certain size.  In this case, if I set 
enable_sort=false, it goes back to the index scan.  If I then set 
enable_indexscan=false, it goes back to sorting.


In response to

Responses

pgsql-performance by date

Next:From: Ron JohnsonDate: 2003-08-30 05:21:17
Subject: Re: Hardware recommendations to scale to silly load
Previous:From: Christopher Kings-LynneDate: 2003-08-30 04:50:31
Subject: Re: sourcecode for newly release eRServer?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group