pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedfinding a max value
3+ messages / 3 participants
[nested] [flat]
* finding a max value
@ 2004-07-02 18:50 Edoardo Ceccarelli <eddy@expot.it>
2004-07-07 19:29 ` Re: finding a max value James Antill <james@and.org>
2004-07-07 20:27 ` Re: [PERFORM] finding a max value Rosser Schwarz <rosser.schwarz@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Edoardo Ceccarelli @ 2004-07-02 18:50 UTC (permalink / raw)
To: pgsql-performance; +Cc: pgsql-admin@postgresql.org
This is the query:
select max(KA) from annuncio
field KA is indexed and is int4,
explaining gives:
explain select max(KA) from annuncio;
QUERY PLAN
-----------------------------------------------------------------------
Aggregate (cost=21173.70..21173.70 rows=1 width=4)
-> Seq Scan on annuncio (cost=0.00..20326.76 rows=338776 width=4)
(2 rows)
wasn't supposed to do an index scan? it takes about 1sec to get the result.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: finding a max value
2004-07-02 18:50 finding a max value Edoardo Ceccarelli <eddy@expot.it>
@ 2004-07-07 19:29 ` James Antill <james@and.org>
1 sibling, 0 replies; 3+ messages in thread
From: James Antill @ 2004-07-07 19:29 UTC (permalink / raw)
To: Edoardo Ceccarelli <eddy@expot.it>; +Cc: pgsql-performance
Edoardo Ceccarelli <eddy@expot.it> writes:
> This is the query:
> select max(KA) from annuncio
>
> field KA is indexed and is int4,
>
> explaining gives:
> explain select max(KA) from annuncio;
> QUERY PLAN
> -----------------------------------------------------------------------
> Aggregate (cost=21173.70..21173.70 rows=1 width=4)
> -> Seq Scan on annuncio (cost=0.00..20326.76 rows=338776 width=4)
> (2 rows)
>
>
> wasn't supposed to do an index scan? it takes about 1sec to get the result.
This is a known misfeature of max() in postgresql, see...
http://archives.postgresql.org/pgsql-performance/2003-12/msg00283.php
--
# James Antill -- james@and.org
:0:
* ^From: .*james@and\.org
/dev/null
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [PERFORM] finding a max value
2004-07-02 18:50 finding a max value Edoardo Ceccarelli <eddy@expot.it>
@ 2004-07-07 20:27 ` Rosser Schwarz <rosser.schwarz@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Rosser Schwarz @ 2004-07-07 20:27 UTC (permalink / raw)
To: Edoardo Ceccarelli <eddy@expot.it>; +Cc: pgsql-performance; pgsql-admin@postgresql.org
On Fri, 02 Jul 2004 20:50:26 +0200, Edoardo Ceccarelli <eddy@expot.it> wrote:
> This is the query:
> select max(KA) from annuncio
> wasn't supposed to do an index scan? it takes about 1sec to get the result.
> TIP 5: Have you checked our extensive FAQ?
I believe this is a FAQ.
See: http://www.postgresql.org/docs/faqs/FAQ.html#4.8
Try "select KA from annuncio order by KA desc limit 1;"
/rls
--
:wq
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-07-07 20:27 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-07-02 18:50 finding a max value Edoardo Ceccarelli <eddy@expot.it>
2004-07-07 19:29 ` James Antill <james@and.org>
2004-07-07 20:27 ` Rosser Schwarz <rosser.schwarz@gmail.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