X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 91D459DCD00 for ; Fri, 24 Feb 2006 10:13:41 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 29668-03 for ; Fri, 24 Feb 2006 10:13:39 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id 65D8E9DC822 for ; Fri, 24 Feb 2006 10:13:33 -0400 (AST) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 5F78E30BAC; Fri, 24 Feb 2006 15:13:37 +0100 (MET) From: "jcfischer" X-Newsgroups: pgsql.performance Subject: nested query on last n rows of huge table Date: 24 Feb 2006 06:13:31 -0800 Organization: http://groups.google.com Lines: 35 Message-ID: <1140790410.923941.261250@p10g2000cwp.googlegroups.com> Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Complaints-To: groups-abuse@google.com User-Agent: G2/0.2 X-HTTP-UserAgent: Mozilla/5.0 (Macintosh; U; PPC Mac OS X Mach-O; de; rv:1.8.0.1) Gecko/20060111 Firefox/1.5.0.1,gzip(gfe),gzip(gfe) Complaints-To: groups-abuse@google.com Injection-Info: p10g2000cwp.googlegroups.com; posting-host=195.186.55.199; posting-account=y8Yb_A0AAAAqh4VDa55BdDUMWbuhwj4N To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200602/448 X-Sequence-Number: 17434 Hi list I'm fairly new to Postgres so bear with me. Googling and searching the list, I didn't find anything that resembled my problem. I have a large table with ca. 10 million inserts per day (fairly simple data: timestam, a couple of id's and a varchar message) I run a query every couple of minutes that looks at the new entries since the last run and retrieves them for further processing (using a WHERE eventtime > '2006-02-24 14:00:00' ) to limit to the most recent entries These queries run around 40-50 seconds (largely due to some LIKE %msg% threwn in for good measure). Postgres performs a seq table scan on those queries :-( My idea is to limit the search to only the last n entries because I found that a SELECT * from table ORDER eventtime DESC limit 1000 is very fast. Because the inserts are in chronolgical order, I can store the sequential id of the highest row from the last query and subtract that from the current high row count to determine that number. Is there a way to limit the expensive query to only those last 1000 (or whatever) results? I have tried to nest SELECTS but my SQL-fu is to limited to get anything through the SQL processor :-) thanks Jens-Christian Fischer