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

Re: Planner having way wrong estimate for group aggregate

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Steinar H(dot) Gunderson" <sgunderson(at)bigfoot(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Planner having way wrong estimate for group aggregate
Date: 2004-09-18 19:48:13
Message-ID: 7261.1095536893@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
"Steinar H. Gunderson" <sgunderson(at)bigfoot(dot)com> writes:
> Now, my first notion was creating a functional index to help the planner:
> ...
> However, this obviously didn't help the planner (this came as a surprise to
> me, but probably won't come as a surprise to the more seasoned users here :-)

7.4 doesn't have any statistics on expression indexes.  8.0 will do what
you want though.  (I just fixed an oversight that prevented it from
doing so...)

> Actually, it seems that the higher I set statistics on "tid", the worse the
> estimate becomes.

I believe that the estimate of number of groups will be exactly the same
as the estimate of the number of values of tid --- there's no knowledge
that date_trunc() might reduce the number of distinct values.

> Any ideas for speeding this up?

In 7.4, the only way I can see to force this to use a hash aggregate is
to temporarily set enable_sort false or raise sort_mem.

			regards, tom lane

In response to

Responses

pgsql-performance by date

Next:From: Steinar H. GundersonDate: 2004-09-18 20:03:36
Subject: Re: Planner having way wrong estimate for group aggregate
Previous:From: Shachar ShemeshDate: 2004-09-18 17:02:48
Subject: Re: Tryint to match Solaris-Oracle performance with directio?

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