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 table.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