Benjamin Arai wrote: > Hi, > > I upgraded to 8.2.4 but there was no significant change in performance. > I did notice that hte query appears to be executed incorrectly. > I have pasted the EXPLAIN ANALYZE below to illustrate: > > =# explain analyze select s_content,textdir from (SELECT * from > text_search WHERE tb_id='P2_TB00001') AS a where path_id='4'; What's wrong with a plain select * from text_search where tb_id='P2_TB00001' and path_id=4; ? You posted the explain output of something like that earlier, but that was on an older pg 8 and without analyze. > > QUERY PLAN > ----------------------------------------------------------------------------------------------------------------------------------------------------- > > Bitmap Heap Scan on text_search (cost=39864.98..59746.59 rows=5083 > width=36) (actual time=7418.651..7418.863 rows=52 loops=1) > Recheck Cond: ((path_id = 4) AND (tb_id = 'P2_TB00001'::text)) > -> BitmapAnd (cost=39864.98..39864.98 rows=5083 width=0) (actual > time=6706.928..6706.928 rows=0 loops=1) > -> Bitmap Index Scan on idx_search_path_id > (cost=0.00..16546.34 rows=1016571 width=0) (actual > time=6609.458..6609.458 rows=52777 loops=1) The row estimates are off by a factor 20 (~1M rows estimated, 50k rows actual). Are you sure you analyze'd recently? -- Alban Hertroys alban@xxxxxxxxxxxxxxxxx magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World //
