X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id C3796D1D614 for ; Wed, 21 Apr 2004 12:13:24 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 00895-06 for ; Wed, 21 Apr 2004 12:13:18 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 52415D1BB64 for ; Wed, 21 Apr 2004 12:13:18 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i3LFDISx014812 for ; Wed, 21 Apr 2004 15:13:18 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i3LEjuIO009259 for pgsql-sql@postgresql.org; Wed, 21 Apr 2004 14:45:56 GMT From: francescosaf X-Newsgroups: comp.databases.postgresql.sql Subject: problem with slow select Date: Wed, 21 Apr 2004 10:00:23 -0500 Message-ID: Organization: Web Servers forum User-Agent: www.webservertalk.com news gateway X-Newsreader: www.webservertalk.com news gateway X-Originating-IP: 147.162.2.222 Lines: 27 X-Complaints-To: abuse@giganews.com X-DMCA-Notifications: http://www.giganews.com/info/dmca.html X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your complaint properly X-Postfilter: 1.1 To: pgsql-sql@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=1.1 tagged_above=0.0 required=5.0 tests=LINES_OF_YELLING, NO_DNS_FOR_FROM X-Spam-Level: * X-Archive-Number: 200404/285 X-Sequence-Number: 17660 hi I have two tables: table: XXXX idcode varchar(15) -->references (idcode) table YYYY latitude float longitude float time timestamp p_a char(1) barcode.... address.... idprog serial... -->primary key flag boolean table YYYY idfact numeric(7,0) --->references ..... idcode varchar(15) --->primary key name varchar(20) I want to exctract the last operation of table XXXX for each idcode of table YYYY where idfact=123 I HAVE THIS QUERY BUT IT IS TOO SLOW (10 SECONDS): select XXXX.*,YYYY.name from YYYY,XXXX join (select XXXX.idcode,max(XXXX.tempo) as tempo from XXXX,YYYY where YYYY.idfact=123 and XXXX.idcode=YYYY.idcode group by XXXX.idcode) temptable on (temptable.tempo=XXXX.tempo and temptable.idcode=XXXX.idcode) where YYYY.idfact=123 and XXXX.idcode=YYYY.idcode order by YYYY.name; PLEASE HELP ME -- francescosaf ------------------------------------------------------------------------ Posted via http://www.webservertalk.com ------------------------------------------------------------------------ View this thread: http://www.webservertalk.com/message189539.html