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.
