Hi,
Apparently, the amount of free space on the partition makes a big
difference in performance. I went from about 30% free space to about
5% and this triggered the performance issues. As soon as freed up
the drive to about 30% free space again the performance issues went
away.
Benjamin
On Apr 24, 2007, at 1:12 AM, Alban Hertroys wrote:
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
[EMAIL PROTECTED]
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 //
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
