To: Brian Connolly <bconn@labkey.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Poor query plan chosen in 9.0.3 vs 8.3.7 
In-reply-to: <BANLkTin2HYy2m_Db354XMf=gDxxf7R1eCg@mail.gmail.com> 
References: <BANLkTin2HYy2m_Db354XMf=gDxxf7R1eCg@mail.gmail.com>
Comments: In-reply-to Brian Connolly <bconn@labkey.com>
	message dated "Thu, 05 May 2011 10:43:37 -0700"
Date: Thu, 05 May 2011 16:27:15 -0400
Message-ID: <9177.1304627235@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Brian Connolly <bconn@labkey.com> writes:
> Any help that you can provide would be greatly appreciated.

I'd suggest trying to get rid of the weird little subselects, like this
one:

>  ... SELECT * FROM assayresult."c69d129_particle_size_result_fields"
>  WHERE (((SELECT Container FROM exp.Data WHERE RowId = DataId) IN
> ('d938da12-1b43-102d-a8a2-78911b79dd1c'))) ...

If you turned that into a regular join between
c69d129_particle_size_result_fields and Data, the planner probably
wouldn't be nearly as confused about how many rows would result.
It's the way-off rowcount estimate for this construct that's
causing most of the problem, AFAICS:

                                       ->  Seq Scan on c69d129_particle_size_result_fields  (cost=0.00..229742.02 rows=348 width=59) (actual time=0.018..572.402 rows=69654 loops=1)
                                             Filter: (((SubPlan 3))::text = 'd938da12-1b43-102d-a8a2-78911b79dd1c'::text)
                                             SubPlan 3
                                               ->  Index Scan using pk_data on data  (cost=0.00..3.27 rows=1 width=37) (actual time=0.004..0.005 rows=1 loops=69654)
                                                     Index Cond: (rowid = $2)

			regards, tom lane