From: Scott Marlowe <scott.marlowe@gmail.com>
To: Justin <justin@emproshunts.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: need to speed up query
Date: Mon, 5 May 2008 21:27:03 -0600
Message-ID: <dcc563d10805052027t48fe3793g882f7b522c220a82@mail.gmail.com> (raw)
In-Reply-To: <481FBC0D.7010509@emproshunts.com>
References: <481FBC0D.7010509@emproshunts.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: scott.marlowe@gmail.com, justin@emproshunts.com
Subject: Re: need to speed up query
In-Reply-To: <dcc563d10805052027t48fe3793g882f7b522c220a82@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox