From: Justin <justin@emproshunts.com>
To: Scott Marlowe <scott.marlowe@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: need to speed up query
Date: Tue, 06 May 2008 00:36:36 -0500
Message-ID: <481FEE64.2@emproshunts.com> (raw)
In-Reply-To: <dcc563d10805052027t48fe3793g882f7b522c220a82@mail.gmail.com>
References: <481FBC0D.7010509@emproshunts.com>
<dcc563d10805052027t48fe3793g882f7b522c220a82@mail.gmail.com>
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.
>
>
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: justin@emproshunts.com, scott.marlowe@gmail.com
Subject: Re: need to speed up query
In-Reply-To: <481FEE64.2@emproshunts.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