Re: need to speed up query

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.
>
>   

-- 
Sent via pgsql-performance mailing list (pgsql-performance@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance