Received: from localhost (unknown [200.46.204.184]) by developer.postgresql.org (Postfix) with ESMTP id 6A8102E0048 for ; Tue, 6 May 2008 00:27:09 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 42887-05 for ; Tue, 6 May 2008 00:27:05 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.181]) by developer.postgresql.org (Postfix) with ESMTP id 5C2FE2E0047 for ; Tue, 6 May 2008 00:27:04 -0300 (ADT) Received: by wa-out-1112.google.com with SMTP id n4so1046691wag.5 for ; Mon, 05 May 2008 20:27:03 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=pyV/gvQJ81GJQHGo9TW4XDTf6NmFJF1jeFw6YMfJ9MQ=; b=Hv8KGKjz1qqnYiOrmCzJojtZCtTZY53DdexWStZz1fQyjc1JDhtC1HFwh7V0QH8OcNyxLQcdr2nbnypVXezq7w1RZaiPL18fQapurCGgVsxDxZ6xw3oy83sxllcUb+DFNHfmEoOujwI3F80IjFLpKp2OSdEQko75GwEG+UMpapY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=nWyFpBFnWmQ05Q6UIH2TrU0rwN5iAstTr+INq+qWrxwD5fs9NqJWnvUHnRz+kZkwXIM69CLYnobbHaEfV1AgCJDBGnq7DWIQjYMmMaGBshhWhCZ7SktqahFzCn+Ih7C+P7vvHTY2YgGAejaPAOAmBtBtrR1cewjtqmGKiT/ImqQ= Received: by 10.115.54.7 with SMTP id g7mr194437wak.212.1210044423632; Mon, 05 May 2008 20:27:03 -0700 (PDT) Received: by 10.114.137.7 with HTTP; Mon, 5 May 2008 20:27:03 -0700 (PDT) Message-ID: Date: Mon, 5 May 2008 21:27:03 -0600 From: "Scott Marlowe" To: Justin Subject: Re: need to speed up query Cc: pgsql-performance@postgresql.org In-Reply-To: <481FBC0D.7010509@emproshunts.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <481FBC0D.7010509@emproshunts.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200805/66 X-Sequence-Number: 30034 You're joining these two tables: period, accnt, but I'm not seeing an on () clause or a where clause joining them. Is the cross product intentional? But what I'm seeing that seems like the lowest hanging fruit would be two column indexes on the bits that are showing up in those bit map scans. Like this part: " Recheck Cond: ((gltrans_date <= $3) AND (gltrans_date >= $0) AND gltrans_accnt_id = $1))" " Filter: gltrans_posted" " -> BitmapAnd (cost=38.90..38.90 rows=10 width=0) (actual time=0.839..0.839 rows=0 loops=1729)" " -> Bitmap Index Scan on gltrans_gltrans_date_idx (cost=0.00..8.08 rows=382 width=0) (actual time=0.782..0.782 rows=5872 loops=1729)" " Index Cond: ((gltrans_date <= $3) AND (gltrans_date >= $0))" " -> Bitmap Index Scan on gltrans_gltrans_accnt_id_idx (cost=0.00..30.57 rows=1908 width=0) (actual time=0.076..0.076 rows=574 loops=798)" " Index Cond: (gltrans_accnt_id = $1)" You are looking through 574 rows in one column and 5872 in another. But when they're anded together, you get 0 rows. A two column index there should really help.