Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 041E7632D56 for ; Thu, 30 Jul 2009 06:38:43 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 10993-03 for ; Thu, 30 Jul 2009 09:38:32 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from na3sys009aog105.obsmtp.com (na3sys009aog105.obsmtp.com [74.125.149.75]) by mail.postgresql.org (Postfix) with SMTP id 833FD63221A for ; Thu, 30 Jul 2009 06:38:31 -0300 (ADT) Received: from source ([209.85.219.206]) by na3sys009aob105.postini.com ([74.125.148.12]) with SMTP ID DSNKSnFqE6saae7kJo4p5IRE60Mn697Gmsnc@postini.com; Thu, 30 Jul 2009 02:38:31 PDT Received: by mail-ew0-f206.google.com with SMTP id 2so569405ewy.43 for ; Thu, 30 Jul 2009 02:38:27 -0700 (PDT) From: Heigo Niilop MIME-Version: 1.0 X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: AcoQ9TuGwljincaNRfyzsTpu7HcPwQAA+Reg Date: Thu, 30 Jul 2009 12:38:16 +0300 Received: by 10.210.140.9 with SMTP id n9mr1076766ebd.26.1248946705747; Thu, 30 Jul 2009 02:38:25 -0700 (PDT) Message-ID: <-2509838136630139343@unknownmsgid> Subject: SELECT max(time) group by problem To: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=001517475e6ebd4fe7046fe9102e 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: 200907/150 X-Sequence-Number: 32891 --001517475e6ebd4fe7046fe9102e 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 table.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 --001517475e6ebd4fe7046fe9102e 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 =A0WHERE

table.id=3Dtable1.id and

table1.id in (1,2,3= ) GROUP BY table.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

--001517475e6ebd4fe7046fe9102e--