Hi All,

I have some problems with my sql query :

select distinct
INTEGER_VALUE,DATE_VALUE,EI_ID,VALUE_TYPE,FLOAT_VA LUE,ID,TEXT_VALUE,CATEGORY_ID,STRING_VALUE,CATEGOR YATTR_ID,NAME from ((( select d_attribute as reqin2 where reqin2.CATEGORYATTR_ID = 1041947543 AND reqin2.TEXT_VALUE ilike '%autrefois%' and ei_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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.3 (GNU/Linux)

iD8DBQBFYsUZCDKlmlhpwnERArhlAJ9FdqTTTCCImBqudrNk9M 9l6p//zgCfS9Jr
aLUKQaY5191Zqj6Rl99Z0z0=
=Njp9
-----END PGP SIGNATURE-----

=?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=1394 loops=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.534 rows=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.590 rows=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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

> 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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate