Postgres Pro
Downloads
Home   >   mailing lists

Thread: problem with max and not using index on PK

problem with max and not using index on PK

From
pginfo
Date:
Hi,
I am using PG 7.3.1

The query plan is:

mydb=# explain analyze select max(IDS) from t_sk10;                                                      QUERY PLAN

------------------------------------------------------------------------------------------------------------------------
Aggregate  (cost=61742.78..61742.78 rows=1 width=64) (actual
time=11221.42..11221.43 rows=1 loops=1)  ->  Seq Scan on t_sk10  (cost=0.00..60076.22 rows=666622 width=64)
(actual time=16.59..9968.00 rows=666622 loops=1)Total runtime: 11221.50 msec
(3 rows)

Time: 11222.68 ms

The time is very big.
For example if I start it on oracle, it take ~ 100 ms !

The column IDS is the PK. It is declared as name.
I do not know the reason for not using index in this case.

Also if I get another (not indexed column) and start also max, PG i
working 4 time quicker as oracle and it is great.

Regards,
ivan.

Re: problem with max and not using index on PK

From
Philip Warner
Date:
At 11:12 AM 3/05/2003 +0200, pginfo wrote:
>I do not know the reason for not using index in this case.

PostgreSQL does not know that it can use the index, and the query example 
you gave is about the only case in which it can use the index. You can get 
it to use an index by rewriting it as:
    select IDS from t_sk10 order by IDS desc limit 1;

...nowhere near as nice, but it works.



----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 03 5330 3172          |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Re: problem with max and not using index on PK

From
pginfo
Date:
It is working in this case.
I see that using "order by" make the query slower by 25% if I start it on
another column.

Alse as I wrote in oracle the max use indexes.

regards,
ivan.

Philip Warner wrote:

> At 11:12 AM 3/05/2003 +0200, pginfo wrote:
> >I do not know the reason for not using index in this case.
>
> PostgreSQL does not know that it can use the index, and the query example
> you gave is about the only case in which it can use the index. You can get
> it to use an index by rewriting it as:
>
>      select IDS from t_sk10 order by IDS desc limit 1;
>
> ...nowhere near as nice, but it works.
>
> ----------------------------------------------------------------
> Philip Warner                    |     __---_____
> Albatross Consulting Pty. Ltd.   |----/       -  \
> (A.B.N. 75 008 659 498)          |          /(@)   ______---_
> Tel: (+61) 0500 83 82 81         |                 _________  \
> Fax: (+61) 03 5330 3172          |                 ___________ |
> Http://www.rhyme.com.au          |                /           \|
>                                   |    --________--
> PGP key available upon request,  |  /
> and from pgp5.ai.mit.edu:11371   |/