Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 1A1AA633C17 for ; Sun, 5 Apr 2009 20:46:37 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 10841-05 for ; Sun, 5 Apr 2009 20:46:35 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from yx-out-2324.google.com (yx-out-2324.google.com [74.125.44.29]) by mail.postgresql.org (Postfix) with ESMTP id CE182633100 for ; Sun, 5 Apr 2009 20:46:35 -0300 (ADT) Received: by yx-out-2324.google.com with SMTP id 8so1300313yxm.73 for ; Sun, 05 Apr 2009 16:46:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=sFlei1dt1XXeUpLpXTfTxhGDIpJhE8r156EDSdBFrqI=; b=cjgzKDKmTi+fTV1w7ApCoEQkFvxb4vDpH5PzFVupN6R5cobSUI31aBZwh6+jxpGlf4 Sn5EMVTHE/LgFhxkGGYEyzwg1/JWJ66AiABxmZZONvSudbb/XjqqO0ZhFTKOZisgWOw3 eQvvugspOr105ofzjqRqtbFja477+zFVjVsU8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=XIqXpELkv7xVWxqcma6U4I5Yjs8lkNzPy0IiKVgMM+ZikuYWj78XaMTZpZS0b6fyLR mAqfd4iqwQnPWs4GQuDEUgos2/iI9mjIAKJPaot1jIxJiYDyriyEmUIMfska0bPHFuGD qYySkg0yAvq9Mh+YO2ZV/1Bu1Tw228RJhoeKo= MIME-Version: 1.0 Received: by 10.151.13.9 with SMTP id q9mr7219357ybi.180.1238975194457; Sun, 05 Apr 2009 16:46:34 -0700 (PDT) In-Reply-To: <003001c9b645$e8a7cec0$b9f76c40$@com> References: <003001c9b645$e8a7cec0$b9f76c40$@com> Date: Sun, 5 Apr 2009 19:46:34 -0400 Message-ID: Subject: Re: difficulties with time based queries From: David Wilson To: Rainer Mager Cc: pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200904/110 X-Sequence-Number: 33477 On Sun, Apr 5, 2009 at 7:26 PM, Rainer Mager wrote: > Bitmap Heap Scan on ad_log=A0 (cost=3D73372.57..3699152.24 rows=3D2488252 > width=3D32) (actual time=3D49792.862..64611.255 rows=3D2268490 loops=3D1) > > =A0=A0 Recheck Cond: ((date(start_time) < '2009-03-31'::date) AND > (date(start_time) >=3D '2009-03-30'::date)) > > =A0=A0 ->=A0 Bitmap Index Scan on ad_log_date_all=A0 (cost=3D0.00..72750.= 51 > rows=3D2488252 width=3D0) (actual time=3D49776.332..49776.332 rows=3D2268= 490 > loops=3D1) > > =A0=A0=A0=A0=A0=A0=A0=A0 Index Cond: ((date(start_time) < '2009-03-31'::d= ate) AND > (date(start_time) >=3D '2009-03-30'::date)) > > =A0Total runtime: 65279.352 ms The stats look good and it's using a viable index for your query. What kind of hardware is this on, and what are the relevant postgresql.conf lines? (Or, for that matter, what does iostat say while this query's running?) --=20 - David T. Wilson david.t.wilson@gmail.com