pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
finding 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