From heigo.niilop@oskando.ee Thu Jul 30 09:38:43 2009 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-- From lyondif02@free.fr Fri Jul 31 00:48:26 2009 Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id E23506338AF for ; Thu, 30 Jul 2009 21:48:26 -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 93149-03 for ; Fri, 31 Jul 2009 00:48:08 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp.insa-lyon.fr (criges14.insa-lyon.fr [134.214.76.242]) by mail.postgresql.org (Postfix) with ESMTP id BF51B63307C for ; Thu, 30 Jul 2009 21:48:15 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by smtp.insa-lyon.fr (Postfix) with ESMTP id 1605BF11D2; Fri, 31 Jul 2009 02:48:13 +0200 (CEST) X-Virus-Scanned: SMTP at INSA-LYON Received: from smtp.insa-lyon.fr ([127.0.0.1]) by localhost (criges14.insa-lyon.fr [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id Gq7upA2hn3eR; Fri, 31 Jul 2009 02:48:12 +0200 (CEST) Received: from [127.0.0.1] (liris.univ-lyon1.fr [134.214.142.10]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) (Authenticated sender: nhandrianar) by smtp.insa-lyon.fr (Postfix) with ESMTPSA id CE2F7F11D1; Fri, 31 Jul 2009 02:48:11 +0200 (CEST) Message-ID: <4A723EF7.20003@free.fr> Date: Fri, 31 Jul 2009 02:46:47 +0200 From: nha Organization: Lyon, Rhone, France User-Agent: Thunderbird 2.0.0.6 (Macintosh/20070728) MIME-Version: 1.0 To: Heigo Niilop CC: PgSQL-sql Subject: Re: SELECT max(time) group by problem References: <-2509838136630139343@unknownmsgid> In-Reply-To: <-2509838136630139343@unknownmsgid> Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=-10 required=5 tests=none X-Spam-Level: X-Archive-Number: 200907/159 X-Sequence-Number: 32900 Hello, Le 30/07/09 11:38, Heigo Niilop a écrit : > 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) > ) > [...] > SELECT MAX(table.timest) FROM table, table1 WHERE > table.id=table1.id and table1.id in (1,2,3) GROUP BY table.id > > [...] 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. [...] Q1) EXPLAIN ANALYZE output would likely give (at least partially) some clarification about observed performance. According to "table" definition, the implicit index created on "table_pkey" would not be efficiently used in the 1st query because of explicit aggregation on column "id" (ie. partial key of "table"). Full scan of "table" is assumed for the join despite index scan on "table1" with "id" index. Each "table1" row is then joined with million of rows of "table" before matching WHERE clauses (as these latter apply for each row resulting from join). Slowness is expected. 3 ideas (with or without combination) of improvement come to my mind at this point: ID1- Definition of explicit index on "table.id": this would lightly quicken aggregation by "id" and join on "id"; ID2- Aggregation on "table1.id" instead of "table.id": because of reference declaration of "table.id" on "table1.id", "table1.id" is assumed to be a (primary) key of "table1" (and thence bound to an index, speeding up aggregation); ID3- Integration of WHERE clause "table1.id IN (1,2,3)" into a subquery on "table1" (because this filter is independent from "table") and use of this subquery instead of "table1" call for join. This would reduce the size of the table to join to "table" and thence reduce the number of join rows at a sooner stage of query execution. A possible rewritten query would express as follows: SELECT MAX(t.timest) FROM table t INNER JOIN (SELECT id FROM table1 WHERE id IN (1,2,3)) t1 ON t.id = t1.id GROUP BY t1.id; (In this case, indexing on "table1.id" is not necessary although recommended.) Inviting you to assess this proposal (or a derivative according to ideas 1, 2, and/or 3). A 4th idea may consist in directly looking up "table.id IN (1,2,3)" in case of certainty on these 3 values in column "table1.id". Such a way strengthens the efficiency of an index to declare on "table.id" so that lookup quickens. The query would look like the following: SELECT MAX(timest) FROM table WHERE id IN (1,2,3) GROUP BY id; Q2) On the other hand, the 2nd query seems to take advantage of "table" index on its primay key (id,timest) as the 2 columns are explicitely referred in WHERE clauses. The sub-select would be a bottleneck; but the WHERE clause of this sub-select refers to a literal against column "id", ie. hash join is assumed to be efficient and used in this case by the database engine for a faster evaluation. Eventually I do not guess what you have in mind by "danger" from this 2nd query. Maybe I missed some elements. Regards. -- nha / Lyon / France.