Message-ID: <481FEE64.2@emproshunts.com>
Date: Tue, 06 May 2008 00:36:36 -0500
From: Justin <justin@emproshunts.com>
User-Agent: Thunderbird 2.0.0.14 (Windows/20080421)
MIME-Version: 1.0
To: Scott Marlowe <scott.marlowe@gmail.com>, pgsql-performance@postgresql.org
Subject: Re: need to speed up query
References: <481FBC0D.7010509@emproshunts.com>
	<dcc563d10805052027t48fe3793g882f7b522c220a82@mail.gmail.com>
In-Reply-To: <dcc563d10805052027t48fe3793g882f7b522c220a82@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

yes the cross join is intentional.

Thanks creating the two column index drop processing time to 15 to 17 
seconds
put per period down to 1 second


Scott Marlowe wrote:
> 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.
>
>