From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Jérôme BENOIS <benois(at)argia-engineering(dot)fr> |
Cc: | PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>, Xavier Milliard <milliard(at)argia(dot)fr>, Laurent BELMONTE <belmonte(at)argia(dot)fr> |
Subject: | Re: BitMapScan performance degradation |
Date: | 2006-11-21 15:44:13 |
Message-ID: | 4622.1164123853@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
=?ISO-8859-1?Q?J=E9r=F4me?= BENOIS <benois(at)argia-engineering(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Joe Lester | 2006-11-21 17:03:34 | Slow Query |
Previous Message | Jérôme BENOIS | 2006-11-21 15:35:56 | Re: BitMapScan performance degradation |