From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Brian Connolly <bconn(at)labkey(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Poor query plan chosen in 9.0.3 vs 8.3.7 |
Date: | 2011-05-05 20:27:15 |
Message-ID: | 9177.1304627235@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Brian Connolly <bconn(at)labkey(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Dimitri | 2011-05-06 10:53:07 | Re: VX_CONCURRENT flag on vxfs( 5.1 or later) for performance for postgresql? |
Previous Message | Bosco Rama | 2011-05-05 20:01:05 | Re: Poor query plan chosen in 9.0.3 vs 8.3.7 |