Thread: Planner having way wrong estimate for group aggregate

Hi,

I'm using PostgreSQL 7.4 on a table with ~700.000 rows looking like this:

                                   Table "public.enkeltsalg"
   Column   |           Type           |                       Modifiers
------------+--------------------------+-------------------------------------------------------
 id         | integer                  | not null default nextval('"enkeltsalg_id_seq"'::text)
 kommentar  | text                     | not null default ''::text
 antall     | numeric(14,4)            | not null
 belop      | numeric(10,0)            | not null
 type       | character(1)             | not null
 tid        | timestamp with time zone | default now()
 eksternid  | integer                  |
 kasseid    | integer                  |
 baraapning | integer                  |
 salgspris  | integer                  |
 firma      | integer                  |
 bongid     | integer                  |
Indexes:
    "enkeltsalg_pkey" primary key, btree (id)
    "enkeltsalg_aapn" btree (baraapning)
    "enkeltsalg_aapn_pris" btree (baraapning, salgspris)
    "enkeltsalg_aapn_type" btree (baraapning, "type")
    "enkeltsalg_pris" btree (salgspris)
Check constraints:
    "enkeltsalg_type_valid" CHECK ("type" = 'K'::bpchar OR "type" = 'B'::bpchar OR "type" = 'M'::bpchar OR "type" =
'T'::bpchar)

And I'm doing the query (after VACUUM ANALYZE)

smt=# explain analyze select sum(belop) as omsetning,date_trunc('day',tid) as dato from enkeltsalg group by
date_trunc('day',tid);
                                                            QUERY PLAN
          

----------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=108062.34..114477.98 rows=172735 width=17) (actual time=20977.544..23890.020 rows=361 loops=1)
   ->  Sort  (cost=108062.34..109912.99 rows=740263 width=17) (actual time=20947.372..21627.107 rows=710720 loops=1)
         Sort Key: date_trunc('day'::text, tid)
         ->  Seq Scan on enkeltsalg  (cost=0.00..18010.29 rows=740263 width=17) (actual time=0.091..7180.528
rows=710720loops=1) 
 Total runtime: 23908.538 ms
(5 rows)

Now, as you can see, the GroupAggregate here is _way_ off, so the planner
makes the wrong choice (it should do a hash aggregate). If I set sort_mem to
131072 instead of 16384, it does a hash aggregate (which is 10 seconds
instead of 24), but I can't have sort_mem that high generally.

Now, my first notion was creating a functional index to help the planner:

smt=# create index enkeltsalg_dag on enkeltsalg ( date_trunc('day',tid) );
CREATE INDEX
smt=# vacuum analyze;
VACUUM

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 :-)
):

smt=# explain analyze select sum(belop) as omsetning,date_trunc('day',tid) as dato from enkeltsalg group by
date_trunc('day',tid);
                                                            QUERY PLAN
          

----------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=103809.15..110017.11 rows=175512 width=17) (actual time=21061.357..23917.370 rows=361 loops=1)
   ->  Sort  (cost=103809.15..105585.95 rows=710720 width=17) (actual time=21032.239..21695.674 rows=710720 loops=1)
         Sort Key: date_trunc('day'::text, tid)
         ->  Seq Scan on enkeltsalg  (cost=0.00..17641.00 rows=710720 width=17) (actual time=0.091..7231.387
rows=710720loops=1) 
 Total runtime: 23937.791 ms
(5 rows)

I also tried to increase the statistics on the "tid" column:

smt=# alter table enkeltsalg alter column tid set statistics 500;
ALTER TABLE
smt=# analyze enkeltsalg;
ANALYZE

However, this made the planner only do a _worse_ estimate:

smt=# explain analyze select sum(belop) as omsetning,date_trunc('day',tid) as dato from enkeltsalg group by
date_trunc('day',tid);
                                                            QUERY PLAN
          

----------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=107906.59..114449.09 rows=199715 width=17) (actual time=20947.197..23794.389 rows=361 loops=1)
   ->  Sort  (cost=107906.59..109754.56 rows=739190 width=17) (actual time=20918.001..21588.735 rows=710720 loops=1)
         Sort Key: date_trunc('day'::text, tid)
         ->  Seq Scan on enkeltsalg  (cost=0.00..17996.88 rows=739190 width=17) (actual time=0.092..7166.488
rows=710720loops=1) 
 Total runtime: 23814.624 ms
(5 rows)

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

Also, I was told (on #postgresql :-) ) to include the following information:

smt=# select n_distinct from pg_stats where attname='tid';
 n_distinct
 ------------
  -0.270181
(1 row)

Any ideas for speeding this up?

/* Steinar */
--
Homepage: http://www.sesse.net/

"Steinar H. Gunderson" <sgunderson@bigfoot.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

On Sat, Sep 18, 2004 at 03:48:13PM -0400, Tom Lane wrote:
> 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...)

OK, so I'll have to wait for 8.0.0beta3 or 8.0.0 (I tried 8.0.0beta2, it gave
me zero difference) -- fortunately, I can probably wait at the rate
everything else is progressing here. :-)

/* Steinar */
--
Homepage: http://www.sesse.net/