To: "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
Cc: pgsql-sql@postgresql.org, pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] why is postgres estimating so badly? 
In-reply-to: <01b901c22d7b$2e9e9f90$cab990c1@atc.unican.es> 
References: <01b901c22d7b$2e9e9f90$cab990c1@atc.unican.es>
Comments: In-reply-to "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
	message dated "Wed, 17 Jul 2002 12:17:40 +0200"
Date: Wed, 17 Jul 2002 13:43:50 -0400
Message-ID: <15637.1026927830@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Luis Alberto Amigo Navarro" <lamigo@atc.unican.es> writes:
>  ->  Seq Scan on part  (cost=0.00..12399.00 rows=1 width=4) (actual time=24.88..4076.81 rows=10856 loops=1)

Seems like the major misestimation is above: the LIKE clause on part is
estimated to select just one row, but it selects 10856 of 'em.  Had the
planner realized the number of returned rows would be in the thousands,
it'd likely have used quite a different plan structure.

>   AND part.name LIKE '%green%'

It's difficult for the planner to produce a decent estimate for the
selectivity of an unanchored LIKE clause, since there are no statistics
it can use for the purpose.  We recently changed FIXED_CHAR_SEL in
src/backend/utils/adt/selfuncs.c from 0.04 to 0.20, which would make
this particular case come out better.  (I believe the estimate would
work out to about 320, if part is 200K rows; that should be enough to
produce at least some change of plan.)  You could try patching your
local installation likewise.

			regards, tom lane