pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedproblem with max and not using index on PK
3+ messages / 2 participants
[nested] [flat]
* problem with max and not using index on PK
@ 2003-05-03 09:12 pginfo <pginfo@t1.unisoftbg.com>
0 siblings, 1 reply; 3+ messages in thread
From: pginfo @ 2003-05-03 09:12 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: problem with max and not using index on PK
@ 2003-05-03 12:04 Philip Warner <pjw@rhyme.com.au>
parent: pginfo <pginfo@t1.unisoftbg.com>
0 siblings, 1 reply; 3+ messages in thread
From: Philip Warner @ 2003-05-03 12:04 UTC (permalink / raw)
To: pginfo <pginfo@t1.unisoftbg.com>; pgsql-sql
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 |/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: problem with max and not using index on PK
@ 2003-05-03 12:36 pginfo <pginfo@t1.unisoftbg.com>
parent: Philip Warner <pjw@rhyme.com.au>
0 siblings, 0 replies; 3+ messages in thread
From: pginfo @ 2003-05-03 12:36 UTC (permalink / raw)
To: Philip Warner <pjw@rhyme.com.au>; +Cc: pgsql-sql
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 |/
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-05-03 12:36 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-03 09:12 problem with max and not using index on PK pginfo <pginfo@t1.unisoftbg.com>
2003-05-03 12:04 ` Philip Warner <pjw@rhyme.com.au>
2003-05-03 12:36 ` pginfo <pginfo@t1.unisoftbg.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