Re: Slow query using simple equality operators

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
> alban(at)magproductions(dot)nl
>
> 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 //
>