[SQL] SELECT max() group by problem

[Heigo Niilop]

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