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 60483563AC for ; Sat, 19 Mar 2005 13:16:43 +0000 (GMT) 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 44031-03 for ; Sat, 19 Mar 2005 13:16:40 +0000 (GMT) Received: from rproxy.gmail.com (rproxy.gmail.com [64.233.170.196]) by svr1.postgresql.org (Postfix) with ESMTP id B3A615637B for ; Sat, 19 Mar 2005 13:16:38 +0000 (GMT) Received: by rproxy.gmail.com with SMTP id j1so399432rnf for ; Sat, 19 Mar 2005 05:16:42 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=f3dxD+GcqtUtWz64FBTVRAsUOS2d9WTvm0xY0CZNeayv0tmBN4xlCXCKYHwIBJBzfimwwcPBt/lKW2It6DP/FKeGdiwbxikZ780Ypl9BjTfmoS/4299oFFfWeu8iq0sOSZN1CpaN4BmEOYiJVsne/ZOBEkYhJPy+LVYmfXyqlRY= Received: by 10.38.89.49 with SMTP id m49mr1469331rnb; Sat, 19 Mar 2005 05:16:42 -0800 (PST) Received: by 10.38.126.9 with HTTP; Sat, 19 Mar 2005 05:16:42 -0800 (PST) Message-ID: Date: Sat, 19 Mar 2005 13:16:42 +0000 From: Mike Rylander Reply-To: Mike Rylander To: "Lending, Rune" , pgsql-performance@postgresql.org Subject: Re: queries on huge tables In-Reply-To: Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit References: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.242 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200503/370 X-Sequence-Number: 11075 The most recent version of this thread starts here: http://archives.postgresql.org/pgsql-general/2005-03/msg00321.php . Search the archives for "table partition", "union view" and "partition inherits" and you should find most relevant discussions. Hope that helps! On Thu, 17 Mar 2005 15:01:43 +0100, Lending, Rune wrote: > > Hello all. > > I am having a couple of tables with couple of hundre millions records in > them. The tables contains a timestamp column. > I am almost always interested in getting datas from a specific day or month. > Each day contains aprox. 400.000 entries. > > When I do such queries as " select ... from archive where m_date between > '2005-01-01' and '2005-02-01' group by ... " and so on. > It takes very long. I am having indexes that kicks in, but still it takes > sometime. > > I have splitted the archive table in smaller monthly tables, it then goes a > lot faster, but not fast enough. > > I know simular systems that uses Oracle and gains a lot on performance > because of the partioning. That kind of anoyes me a bit :) > > Does anyone of you have some good ideas on how speed up such queries on > huge tables? > > regards > rune > > > > -- Mike Rylander mrylander@gmail.com GPLS -- PINES Development Database Developer http://open-ils.org