To: =?ISO-8859-1?Q?J=E9r=F4me?= BENOIS <benois@argia-engineering.fr>
cc: PostgreSQL Performance <pgsql-performance@postgresql.org>,
	Xavier Milliard <milliard@argia.fr>, Laurent BELMONTE <belmonte@argia.fr>
Subject: Re: BitMapScan performance degradation 
In-reply-to: <1164100889.5281.184.camel@localhost.localdomain> 
References: <1164100889.5281.184.camel@localhost.localdomain>
Comments: In-reply-to =?ISO-8859-1?Q?J=E9r=F4me?= BENOIS
	<benois@argia-engineering.fr>
	message dated "Tue, 21 Nov 2006 10:21:29 +0100"
Date: Tue, 21 Nov 2006 10:44:13 -0500
Message-ID: <4622.1164123853@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

=?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