To: "Dave Dutcher" <dave@tridecap.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Slow Query on Postgres 8.2 
In-reply-to: <007901c7306b$fd1d2c90$8300a8c0@tridecap.com> 
References: <007901c7306b$fd1d2c90$8300a8c0@tridecap.com>
Comments: In-reply-to "Dave Dutcher" <dave@tridecap.com>
	message dated "Thu, 04 Jan 2007 19:51:16 -0600"
Date: Thu, 04 Jan 2007 22:04:05 -0500
Message-ID: <8870.1167966245@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Dave Dutcher" <dave@tridecap.com> writes:
> Here is the table definition.  Thanks.

[ fools around with it for awhile... ]  I think this is already fixed
for 8.2.1.  Note the costs of the two related index scans:

8.2.0:
         ->  Bitmap Index Scan on om_transaction_om_transaction_index  (cost=0.00..7421.67 rows=488 width=0) (actual time=3411.227..3411.227 rows=0 loops=1)
               Index Cond: ((as_of_date > '2006-12-04'::date) AND (as_of_date <= '2006-12-05'::date) AND ((strategy_id)::text = ANY (('{BASKET1,BASKET2,BASKET3,BASKET4,BASKET5,BASKET6,BASKET7,BASKET8,BASKET9,BASKET10,BASKET11}'::character varying[])::text[])) AND ((owner_trader_id)::text = ANY (('{dave,sam,bob,tad,tim,harry,frank,bart,lisa,homer,marge,maggie,apu,milhouse,"disco stu"}'::character varying[])::text[])) AND (cf_account_id = ANY ('{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,29}'::integer[])))

8.1.2:
               ->  Bitmap Index Scan on om_transaction_om_transaction_index  (cost=0.00..101.69 rows=5949 width=0) (actual time=3.419..3.419 rows=7967 loops=1)
                     Index Cond: ((as_of_date > '2006-12-04'::date) AND (as_of_date <= '2006-12-05'::date))

8.1.2 returns a lot more rows but spends a lot less time doing it.
The reason is that using all those =ANY clauses as index quals is
*expensive* --- they actually trigger multiple scans of the index.
8.2.0 is underestimating their cost.  We fixed that a couple weeks ago
(after some reports from Arjen van der Meijden) and I can't actually get
8.2 branch tip to produce a plan like what you show.

Please try it again when 8.2.1 comes out (Monday) and we'll see if
there's any more tweaking needed.

BTW, it's interesting to note that the plan 8.1.2 produces is pretty
obviously bogus in itself ... why do only the first two arms of the
BitmapOr use as_of_date conditions?  We fixed some sillinesses in the
bitmap scan planning later in the 8.1 series, so I think you'd find
that 8.1.latest does this differently.

			regards, tom lane