SELECT max() group by problem - Mailing list pgsql-sql

From Heigo Niilop
Subject SELECT max() group by problem
Date
Msg-id 1272877363058733026@unknownmsgid
Whole thread Raw
List 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)

)



=84id=93 have foreign key with table1



and when I try to do



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

table.id=3Dtable1.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=3Dtable1.id and table1.id in(1,2,3) and table.timest=3D

(SELECT max(timest) FROM table WHERE table.id=3Dtable1.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

pgsql-sql by date:

Previous
From: Joshua Tolley
Date:
Subject: Re: How fetch multiple rows into one text-field?
Next
From: Axe
Date:
Subject: Re: Tweak sql result set... ?