Обсуждение: BitMapScan performance degradation

Hi All,

    I have some problems with my sql query :

select distinct
INTEGER_VALUE,DATE_VALUE,EI_ID,VALUE_TYPE,FLOAT_VALUE,ID,TEXT_VALUE,CATEGORY_ID,STRING_VALUE,CATEGORYATTR_ID,NAME from
((( select d_attribute as reqin2 where reqin2.CATEGORYATTR_ID = 1041947543 AND reqin2.TEXT_VALUE ilike '%autrefois%'
andei_id in ( select distinct ei_id as EIID from MPNG2_ei_attribute as reqin3 where reqin3.NAME =
'CategoryID-1084520156'AND reqin3.STRING_VALUE = '1084520156' )  )  ) ) ) as req0 join MPNG2_ei_attribute on req0.eiid
=MPNG2_ei_attribute.ei_id   order by ei_id asc; 

    When enable_bitmapscan is enabled this query cost 51893.491 ms and when
is disabled 117.709 ms. But i heard bitmapscan feature improved
performance, can you help me ?

    You can read two results of EXPLAIN ANALYZE command here :
http://sharengo.org/explain.txt

Best Regards,
Jérôme.

--
Jérôme BENOIS
Open-Source : http://www.sharengo.org
Corporate : http://www.argia-engineering.fr
JabberId : jerome.benois AT gmail.com

=?ISO-8859-1?Q?J=E9r=F4me?= BENOIS <benois@argia-engineering.fr> writes:
>     You can read two results of EXPLAIN ANALYZE command here :
> http://sharengo.org/explain.txt

I think the problem is the misestimation of the size of the reqin3
result:

->  Bitmap Heap Scan on mpng2_ei_attribute reqin3  (cost=28.17..32.18 rows=1 width=4) (actual time=1.512..7.941
rows=1394loops=1) 
      Recheck Cond: (((string_value)::text = '1084520156'::text) AND ((name)::text = 'CategoryID-1084520156'::text))
      ->  BitmapAnd  (cost=28.17..28.17 rows=1 width=0) (actual time=1.275..1.275 rows=0 loops=1)
        ->  Bitmap Index Scan on mpng2_ei_attribute_string_value  (cost=0.00..4.78 rows=510 width=0) (actual
time=0.534..0.534rows=1394 loops=1) 
              Index Cond: ((string_value)::text = '1084520156'::text)
        ->  Bitmap Index Scan on mpng2_ei_attribute_name  (cost=0.00..23.13 rows=2896 width=0) (actual
time=0.590..0.590rows=1394 loops=1) 
              Index Cond: ((name)::text = 'CategoryID-1084520156'::text)

Anytime a rowcount estimate is off by more than a factor of a thousand,
you can expect some poor choices in the rest of the plan :-(.  It looks
to me like the planner is expecting those two index conditions to be
independently selective, when in reality they are completely redundant.
Perhaps rethinking your data model would be a useful activity.

            regards, tom lane

> When enable_bitmapscan is enabled this query cost 51893.491 ms and when
> is disabled 117.709 ms. But i heard bitmapscan feature improved
> performance, can you help me ?

The standard question we always ask first is if you have run VACUUM
ANALYZE recently?

Are all the costs and estimated number of rows the same after you have run
VACUUM ANALYZE? If not you might want to show that new plan as well.

/Dennis

Hi Dennis,


Le mardi 21 novembre 2006 à 16:12 +0100, db@zigo.dhs.org a écrit :
> > When enable_bitmapscan is enabled this query cost 51893.491 ms and when
> > is disabled 117.709 ms. But i heard bitmapscan feature improved
> > performance, can you help me ?
>
> The standard question we always ask first is if you have run VACUUM
> ANALYZE recently?

Yes i ran VACCUUM ANALYZE just before my EXPLAIN.

> Are all the costs and estimated number of rows the same after you have run
> VACUUM ANALYZE? If not you might want to show that new plan as well.
>
> /Dennis

Jérôme.