SELECT max() group by problem

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

Browse pgsql-sql by date

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