X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D18F2D1B17A for ; Mon, 26 Jul 2004 12:04:56 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 37009-03 for ; Mon, 26 Jul 2004 15:04:43 +0000 (GMT) Received: from mail.speedfc.com (66-136-75-131.ded.swbell.net [66.136.75.131]) by svr1.postgresql.org (Postfix) with ESMTP id 42B4CD1B4CA for ; Mon, 26 Jul 2004 12:04:42 -0300 (ADT) Received: from [172.25.96.149] (unknown [172.25.96.149]) by mail.speedfc.com (Postfix) with ESMTP id 5397616EC4; Mon, 26 Jul 2004 10:03:22 -0500 (CDT) Message-ID: <41051C1E.90805@speedfc.com> Date: Mon, 26 Jul 2004 09:58:38 -0500 From: Kevin Barnard Organization: SpeedFC User-Agent: Mozilla Thunderbird 0.7.1 (Windows/20040626) X-Accept-Language: en-us, en MIME-Version: 1.0 To: "Harmon S. Nine" , pgsql-performance@postgresql.org Subject: Re: Timestamp-based indexing References: <410519F6.5020806@netarx.com> In-Reply-To: <410519F6.5020806@netarx.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200407/172 X-Sequence-Number: 7563 Harmon S. Nine wrote: > monitor=# explain analyze select * from "eventtable" where timestamp > > CURRENT_TIMESTAMP - INTERVAL '10 minutes'; > QUERY PLAN Try SELECT * FROM eventtable where timestamp BETWEEN (CURRENT_TIMESTAMP - INTERVAL '10 minutes') AND CURRENT_TIMESTAMP; This should will use a range off valid times. What your query is doing is looking for 10 minutes ago to an infinate future. Statically speaking that should encompass most of the table because you have an infinate range. No index will be used. If you assign a range the planner can fiqure out what you are looking for. -- Kevin Barnard Speed Fulfillment and Call Center kbarnard@speedfc.com 214-258-0120