Re: Slow query using simple equality operators

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 //