From: | Heigo Niilop <heigo(dot)niilop(at)oskando(dot)ee> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | SELECT max() group by problem |
Date: | 2009-07-30 09:07:58 |
Message-ID: | 1272877363058733026@unknownmsgid |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Heigo Niilop | 2009-07-30 09:38:16 | SELECT max(time) group by problem |
Previous Message | Jasmin Dizdarevic | 2009-07-30 07:53:01 | Re: Tweak sql result set... ? |