pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedbad estimates / non-scanning aggregates
6+ messages / 2 participants
[nested] [flat]
* bad estimates / non-scanning aggregates
@ 2003-08-29 00:10 Ken Geis <kgeis@speakeasy.org>
0 siblings, 1 reply; 6+ messages in thread
From: Ken Geis @ 2003-08-29 00:10 UTC (permalink / raw)
To: pgsql-performance
I'm surprised at the effort pgsql requires to run one of my queries. I
don't know how to tune this query.
Column | Type | Modifiers
------------+--------------+-----------
the_id | integer | not null
the_date | date | not null
num1 | numeric(9,4) |
num2 | numeric(9,4) |
num3 | numeric(9,4) |
num4 | numeric(9,4) |
int1 | integer |
Indexes:
"the_table_pkey" primary key, btree (the_id, the_date)
---------------------------------------
The query I want to run is
select stock_id, min(price_date) from day_ends group by stock_id;
---------------------------------------
Here's the plan that I get.
GroupAggregate (cost=3711244.30..3838308.31 rows=6732 width=8)
-> Sort (cost=3711244.30..3753593.36 rows=16939624 width=8)
Sort Key: stock_id
-> Seq Scan on day_ends (cost=0.00..361892.24 rows=16939624
width=8)
If I set enable_seqscan = false, the plan changes to
GroupAggregate (cost=0.00..67716299.91 rows=6732 width=8)
-> Index Scan using day_ends_pkey on day_ends
(cost=0.00..67631584.96 rows=16939624 width=8)
---------------------------------------
Now... the first plan uses up tons of temporary space for sorting. The
second one just runs and runs and runs. I've tried setting the
statistics to 1000 with little effect.
So the query can get everything it needs from the index, and a full scan
of the index should be faster (the index file is less than half the size
of the data file.) So why does the optimizer estimate so high?
Also, to get the MIN for a given group, not all values of the index need
to be seen. Must pgsql do a full scan because it treats all aggregates
in the same way? Are MIN and MAX used often enough to justify special
treatment, and could that be cleanly implemented? Perhaps the aggregate
function can request the data in a certain order, be told that it is
being passed data in a certain order, and return before seeing the
entire set of data.
Food for thought...
Thanks,
Ken Geis
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: bad estimates / non-scanning aggregates
@ 2003-08-29 02:50 Ken Geis <kgeis@speakeasy.org>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 6+ messages in thread
From: Ken Geis @ 2003-08-29 02:50 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-performance
Bruno Wolff III wrote:
> On Thu, Aug 28, 2003 at 17:10:31 -0700,
> Ken Geis <kgeis@speakeasy.org> wrote:
>
>>The query I want to run is
>>
>>select stock_id, min(price_date) from day_ends group by stock_id;
>
> The fast way to do this is:
>
> select distinct on (stock_id) stock_id, price_date
> order by stock_id, price_date;
Not according to the optimizer! Plus, this is not guaranteed to return
the correct results.
Unique (cost=3711244.30..3795942.42 rows=6366 width=8)
-> Sort (cost=3711244.30..3753593.36 rows=16939624 width=8)
Sort Key: stock_id, price_date
-> Seq Scan on day_ends (cost=0.00..361892.24 rows=16939624
width=8)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: bad estimates / non-scanning aggregates
@ 2003-08-29 02:51 Bruno Wolff III <bruno@wolff.to>
parent: Ken Geis <kgeis@speakeasy.org>
0 siblings, 1 reply; 6+ messages in thread
From: Bruno Wolff III @ 2003-08-29 02:51 UTC (permalink / raw)
To: Ken Geis <kgeis@speakeasy.org>; +Cc: pgsql-performance
On Thu, Aug 28, 2003 at 17:10:31 -0700,
Ken Geis <kgeis@speakeasy.org> wrote:
> The query I want to run is
>
> select stock_id, min(price_date) from day_ends group by stock_id;
The fast way to do this is:
select distinct on (stock_id) stock_id, price_date
order by stock_id, price_date;
> Also, to get the MIN for a given group, not all values of the index need
> to be seen. Must pgsql do a full scan because it treats all aggregates
> in the same way? Are MIN and MAX used often enough to justify special
> treatment, and could that be cleanly implemented? Perhaps the aggregate
> function can request the data in a certain order, be told that it is
> being passed data in a certain order, and return before seeing the
> entire set of data.
Yes, max and min are not treated special so they don't benefit from
indexes. This has been discussed repeatedly in the archives.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: bad estimates / non-scanning aggregates
@ 2003-08-29 03:00 Ken Geis <kgeis@speakeasy.org>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 6+ messages in thread
From: Ken Geis @ 2003-08-29 03:00 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-performance
Bruno Wolff III wrote:
>>Not according to the optimizer! Plus, this is not guaranteed to return
>>the correct results.
>
> For it to be fast you need an index on (stock_id, price_date) so that
> you can use an index scan.
I already said that such an index existed. In fact, it is the primary
key of the table. And yes, I *am* analyzed!
> The answers are guarenteed to be correct. See:
> http://developer.postgresql.org/docs/postgres/sql-select.html#SQL-DISTINCT
That's good to know. Thanks!
Ken
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: bad estimates / non-scanning aggregates
@ 2003-08-29 03:01 Bruno Wolff III <bruno@wolff.to>
parent: Ken Geis <kgeis@speakeasy.org>
0 siblings, 1 reply; 6+ messages in thread
From: Bruno Wolff III @ 2003-08-29 03:01 UTC (permalink / raw)
To: Ken Geis <kgeis@speakeasy.org>; +Cc: pgsql-performance
On Thu, Aug 28, 2003 at 19:50:38 -0700,
Ken Geis <kgeis@speakeasy.org> wrote:
> Bruno Wolff III wrote:
> >On Thu, Aug 28, 2003 at 17:10:31 -0700,
> > Ken Geis <kgeis@speakeasy.org> wrote:
> >
> >>The query I want to run is
> >>
> >>select stock_id, min(price_date) from day_ends group by stock_id;
> >
> >The fast way to do this is:
> >
> >select distinct on (stock_id) stock_id, price_date
> > order by stock_id, price_date;
>
> Not according to the optimizer! Plus, this is not guaranteed to return
> the correct results.
For it to be fast you need an index on (stock_id, price_date) so that
you can use an index scan.
The answers are guarenteed to be correct. See:
http://developer.postgresql.org/docs/postgres/sql-select.html#SQL-DISTINCT
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: bad estimates / non-scanning aggregates
@ 2003-08-29 03:38 Bruno Wolff III <bruno@wolff.to>
parent: Ken Geis <kgeis@speakeasy.org>
0 siblings, 0 replies; 6+ messages in thread
From: Bruno Wolff III @ 2003-08-29 03:38 UTC (permalink / raw)
To: Ken Geis <kgeis@speakeasy.org>; +Cc: pgsql-performance
On Thu, Aug 28, 2003 at 20:00:32 -0700,
Ken Geis <kgeis@speakeasy.org> wrote:
> Bruno Wolff III wrote:
> >>Not according to the optimizer! Plus, this is not guaranteed to return
> >>the correct results.
> >
> >For it to be fast you need an index on (stock_id, price_date) so that
> >you can use an index scan.
>
> I already said that such an index existed. In fact, it is the primary
> key of the table. And yes, I *am* analyzed!
Your original example didn't actually match that of the table you are showing
examples from. In that example the second half of the primary key was the
date not the end of the day price. If this is the case for the real table,
then that is the reason the distinct on doesn't help.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-08-29 03:38 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-29 00:10 bad estimates / non-scanning aggregates Ken Geis <kgeis@speakeasy.org>
2003-08-29 02:51 ` Bruno Wolff III <bruno@wolff.to>
2003-08-29 02:50 ` Ken Geis <kgeis@speakeasy.org>
2003-08-29 03:01 ` Bruno Wolff III <bruno@wolff.to>
2003-08-29 03:00 ` Ken Geis <kgeis@speakeasy.org>
2003-08-29 03:38 ` Bruno Wolff III <bruno@wolff.to>
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