Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id D4E03632913 for ; Thu, 30 Jul 2009 06:27:18 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 26591-08 for ; Thu, 30 Jul 2009 09:27:02 +0000 (UTC) X-Greylist: delayed 00:08:12.671744 by SQLgrey-1.7.6 Received: from na3sys009aog108.obsmtp.com (na3sys009aog108.obsmtp.com [74.125.149.199]) by mail.postgresql.org (Postfix) with SMTP id EEB0F63221A for ; Thu, 30 Jul 2009 06:27:06 -0300 (ADT) Received: from source ([209.85.219.211]) by na3sys009aob108.postini.com ([74.125.148.12]) with SMTP ID DSNKSnFnaeXeqn425MIyq7+SDIUSOIdgRx/v@postini.com; Thu, 30 Jul 2009 02:27:06 PDT Received: by ewy7 with SMTP id 7so168222ewy.42 for ; Thu, 30 Jul 2009 02:27:04 -0700 (PDT) From: Heigo Niilop MIME-Version: 1.0 X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: AcoQ9TuGwljincaNRfyzsTpu7HcPwQ== Date: Thu, 30 Jul 2009 12:07:58 +0300 Received: by 10.210.131.6 with SMTP id e6mr1393634ebd.29.1248945532008; Thu, 30 Jul 2009 02:18:52 -0700 (PDT) Message-ID: <1272877363058733026@unknownmsgid> Subject: SELECT max() group by problem To: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=0015174c123cc77a86046fe8cad3 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=-10 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200908/1 X-Sequence-Number: 32910 --0015174c123cc77a86046fe8cad3 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: quoted-printable 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 --0015174c123cc77a86046fe8cad3 Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: quoted-printable

hi,

=A0

I have table

=A0

CREATE TABLE table

(

=A0 id integer NOT NULL,

=A0 timest timestamp with time zone NOT NULL,

=A0 db_time timestamp with time zone NOT NULL DEFAUL= T now(),

=A0 "values" text[],

=A0 CONSTRAINT table_pkey PRIMARY KEY (id, timest)

)

=A0

=84id=93 have foreign key with table1

=A0

and when I try to do =A0=A0

=A0

SELECT MAX(table.timest) =A0FROM table, table1 =A0WH= ERE

table.id=3Dtable1.id and

table1.id in (1,2,3= ) GROUP BY id

=A0

then it is terrible slow, when I use strange syntax<= /p>

=A0

SELECT table.timest =A0FROM 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) =A0=

=A0

I receive all needed data very fast.

=A0

My questions are

1)=A0=A0=A0=A0=A0 why this first query is slow and what I can do to make it faster (some more indexes??)?

2)=A0=A0=A0=A0=A0 what kind of danger I have with second query (so far I have right data)? =A0=A0

=A0

=A0

I have Postgres 8.3 and table have over million rows= .

=A0

Regards,

Heigo

=A0

--0015174c123cc77a86046fe8cad3--