pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedBitMapScan performance degradation
4+ messages / 3 participants
[nested] [flat]
* BitMapScan performance degradation
@ 2006-11-21 09:21 Jérôme BENOIS <benois@argia-engineering.fr>
0 siblings, 2 replies; 4+ messages in thread
From: Jérôme BENOIS @ 2006-11-21 09:21 UTC (permalink / raw)
To: pgsql-performance; +Cc: Xavier Milliard <milliard@argia.fr>; Laurent BELMONTE <belmonte@argia.fr>
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%' 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
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BitMapScan performance degradation
@ 2006-11-21 15:12 db@zigo.dhs.org
parent: Jérôme BENOIS <benois@argia-engineering.fr>
1 sibling, 1 reply; 4+ messages in thread
From: db@zigo.dhs.org @ 2006-11-21 15:12 UTC (permalink / raw)
To: Jérôme BENOIS <benois@argia-engineering.fr>; +Cc: pgsql-performance; Xavier Milliard <milliard@argia.fr>; Laurent BELMONTE <belmonte@argia.fr>
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BitMapScan performance degradation
@ 2006-11-21 15:35 Jérôme BENOIS <benois@argia-engineering.fr>
parent: db@zigo.dhs.org
0 siblings, 0 replies; 4+ messages in thread
From: Jérôme BENOIS @ 2006-11-21 15:35 UTC (permalink / raw)
To: db@zigo.dhs.org; +Cc: pgsql-performance; Xavier Milliard <milliard@argia.fr>; Laurent BELMONTE <belmonte@argia.fr>
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.
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BitMapScan performance degradation
@ 2006-11-21 15:44 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jérôme BENOIS <benois@argia-engineering.fr>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2006-11-21 15:44 UTC (permalink / raw)
To: Jérôme BENOIS <benois@argia-engineering.fr>; +Cc: pgsql-performance; Xavier Milliard <milliard@argia.fr>; Laurent BELMONTE <belmonte@argia.fr>
=?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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-11-21 15:44 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-11-21 09:21 BitMapScan performance degradation Jérôme BENOIS <benois@argia-engineering.fr>
2006-11-21 15:12 ` db@zigo.dhs.org
2006-11-21 15:35 ` Jérôme BENOIS <benois@argia-engineering.fr>
2006-11-21 15:44 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox