Обсуждение:

Hello everybody!
I need some help with a simple query.
I've got a problem with getting a maximum value from a very large table
(10000000+ rows):
I have table:
CREATE TABLE TABLE_A
(
COL1 INT,
COL2 INT,
CONSTRAINT PK PRIMARY KEY (COL1, COL2)
)

and when I want to get the maximum value for col1:
SELECT MAX(COL1) FROM TABLE_A WHERE COL2 = 1
it takes a large amount of time.
I created an index on column COL1, but it doesn't work.
    Bc. Ferenc Lengyel
    Address:        Prievozská 6/A, Bratislava 821 09,
Slovakia    Phone Number:    +421 (7) 58222 222, +421 (7) 58222 407    E-mail address:    mailto:lengyel@ditec.sk

Hello Lengyel,

Once, Monday, October 30, 2000, 5:36:23 PM, you wrote:

LF> Hello everybody!
LF> I need some help with a simple query.
LF> I've got a problem with getting a maximum value from a very large table
LF> (10000000+ rows):
LF> I have table:
LF> CREATE TABLE TABLE_A
LF> (
LF> COL1 INT,
LF> COL2 INT,
LF> CONSTRAINT PK PRIMARY KEY (COL1, COL2)
LF> )

LF> and when I want to get the maximum value for col1:
LF> SELECT MAX(COL1) FROM TABLE_A WHERE COL2 = 1
LF> it takes a large amount of time.
LF> I created an index on column COL1, but it doesn't work.

You need to create index on COL2.

-- 
Best regards,Yury  ICQ 11831432mailto:yura@vpcit.ru

Can I be removed from this mailing list?

> -----Original Message-----
> From:    Yury Don [SMTP:yura@vpcit.ru]
> Sent:    Monday, October 30, 2000 6:55 AM
> To:    pgsql-sql@postgresql.org
> Subject:    Re: [SQL]
> 
> Hello Lengyel,
> 
> Once, Monday, October 30, 2000, 5:36:23 PM, you wrote:
> 
> LF> Hello everybody!
> LF> I need some help with a simple query.
> LF> I've got a problem with getting a maximum value from a very large
> table
> LF> (10000000+ rows):
> LF> I have table:
> LF> CREATE TABLE TABLE_A
> LF> (
> LF> COL1 INT,
> LF> COL2 INT,
> LF> CONSTRAINT PK PRIMARY KEY (COL1, COL2)
> LF> )
> 
> LF> and when I want to get the maximum value for col1:
> LF> SELECT MAX(COL1) FROM TABLE_A WHERE COL2 = 1
> LF> it takes a large amount of time.
> LF> I created an index on column COL1, but it doesn't work.
> 
> You need to create index on COL2.
> 
> -- 
> Best regards,
>  Yury  ICQ 11831432
>  mailto:yura@vpcit.ru
> 
> 

At 13:36 30/10/00 +0100, Lengyel Ferenc wrote:
>Hello everybody!
>I need some help with a simple query.
>I've got a problem with getting a maximum value from a very large table
>(10000000+ rows):

Unfortunately PGSQL does not use indexes for min & max. One approach that
should work is:
   select col1 from TABLE_A WHERE COL2 = 1 order by col2, col1 desc limit 1;

and you will need an index on (col2, col1).


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