From: Heigo Niilop <heigo.niilop@oskando.ee>
To: pgsql-sql@postgresql.org
Subject: SELECT max() group by problem
Date: Thu, 30 Jul 2009 12:07:58 +0300
Message-ID: <1272877363058733026@unknownmsgid> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: heigo.niilop@oskando.ee
Subject: Re: SELECT max() group by problem
In-Reply-To: <1272877363058733026@unknownmsgid>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox