Thread: SELECT max() group by problem

SELECT max() group by problem

From
Heigo Niilop
Date:

hi,

 

I have table

 

CREATE TABLE table

(

  id integer NOT NULL,

  timest timestamp with time zone NOT NULL,

  db_time timestamp with time zone NOT NULL DEFAULT now(),

  "values" text[],

  CONSTRAINT table_pkey PRIMARY KEY (id, timest)

)

 

„id“ have foreign key with table1

 

and when I try to do   

 

SELECT MAX(table.timest)  FROM table, table1  WHERE

table.id=table1.id and

table1.id in (1,2,3) GROUP BY id

 

then it is terrible slow, when I use strange syntax

 

SELECT table.timest  FROM table,table1 WHERE

table.id=table1.id and table1.id in(1,2,3) and table.timest=

(SELECT max(timest) FROM table WHERE table.id=table1.id)  

 

I receive all needed data very fast.

 

My questions are

1)      why this first query is slow and what I can do to make it faster (some more indexes??)?

2)      what kind of danger I have with second query (so far I have right data)?   

 

 

I have Postgres 8.3 and table have over million rows.

 

Regards,

Heigo