pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPlanner having way wrong estimate for group aggregate
3+ messages / 2 participants
[nested] [flat]
* Planner having way wrong estimate for group aggregate
@ 2004-09-18 17:01 Steinar H. Gunderson <sgunderson@bigfoot.com>
2004-09-18 19:48 ` Re: Planner having way wrong estimate for group aggregate Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Steinar H. Gunderson @ 2004-09-18 17:01 UTC (permalink / raw)
To: pgsql-performance
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=710720 loops=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=710720 loops=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=710720 loops=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/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Planner having way wrong estimate for group aggregate
2004-09-18 17:01 Planner having way wrong estimate for group aggregate Steinar H. Gunderson <sgunderson@bigfoot.com>
@ 2004-09-18 19:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-09-18 20:03 ` Re: Planner having way wrong estimate for group aggregate Steinar H. Gunderson <sgunderson@bigfoot.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2004-09-18 19:48 UTC (permalink / raw)
To: Steinar H. Gunderson <sgunderson@bigfoot.com>; +Cc: pgsql-performance
"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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Planner having way wrong estimate for group aggregate
2004-09-18 17:01 Planner having way wrong estimate for group aggregate Steinar H. Gunderson <sgunderson@bigfoot.com>
2004-09-18 19:48 ` Re: Planner having way wrong estimate for group aggregate Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-09-18 20:03 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
0 siblings, 0 replies; 3+ messages in thread
From: Steinar H. Gunderson @ 2004-09-18 20:03 UTC (permalink / raw)
To: pgsql-performance
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/
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-09-18 20:03 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-18 17:01 Planner having way wrong estimate for group aggregate Steinar H. Gunderson <sgunderson@bigfoot.com>
2004-09-18 19:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-09-18 20:03 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox