BUG #6579: negative cost in a planning

The following bug has been logged on the website:

Bug reference: 6579
Logged by: Istvan Endredy
Email address: istvan(dot)endredy(at)gmail(dot)com
PostgreSQL version: 9.1.3
Operating system: linux
Description:

Hi,

I've reduced the problem to this situation:

there is a table with 3000 rows, and a custom index with a function.
This query gives negative cost:
select distinct name
from negativeCostBugReport t_
where noaccent(t_.name) like 'B%' limit 10

plan:
Limit (cost=-170.35..-170.31 rows=4 width=2) (actual time=17.399..17.401
rows=1 loops=1)
-> HashAggregate (cost=-170.35..-170.31 rows=4 width=2) (actual
time=17.397..17.398 rows=1 loops=1)
-> Index Scan using negativecostbugreport_noaccent_idx on
negativecostbugreport t_ (cost=1.25..-171.22 rows=346 width=2) (actual
time=0.240..16.852 rows=346 loops=1)
Index Cond: (((noaccent(name))::text >= 'B'::text) AND
((noaccent(name))::text < 'C'::text))
Filter: ((noaccent(name))::text ~~ 'B%'::text)
Total runtime: 17.450 ms

(sorry, but http://explain.depesz.com/ cannot parse this)

schema:

CREATE TABLE negativecostbugreport
(
id integer NOT NULL DEFAULT nextval('product_parent_id_seq'::regclass),
name character varying NOT NULL,
CONSTRAINT negativecostbugreport_pkey PRIMARY KEY (id )
);

CREATE INDEX negativecostbugreport_noaccent_idx
ON negativecostbugreport
USING btree
(noaccent(name) COLLATE pg_catalog."C" );

CREATE OR REPLACE FUNCTION noaccent(character varying)
RETURNS character varying AS
$BODY$select to_ascii(convert_to($1, 'latin2'), 'latin2')$BODY$
LANGUAGE sql IMMUTABLE STRICT
COST 250;

CREATE OR REPLACE FUNCTION to_ascii(bytea, name)
RETURNS text AS
'to_ascii_encname'
LANGUAGE internal VOLATILE
COST 1;

If you need any further details, feel free to ask.

Thanks for your work! :)

Best Regards,
Istvan

istvan(dot)endredy(at)gmail(dot)com writes:
> there is a table with 3000 rows, and a custom index with a function.
> This query gives negative cost:
> select distinct name
> from negativeCostBugReport t_
> where noaccent(t_.name) like 'B%' limit 10

Hm, interesting. The culprit seems to be the part of cost_index that
estimates the per-tuple cost of evaluating filter conditions. It's
trying to do that by taking the baserestrictcost (here, that'll be
exactly the cost of the filter condition noaccent(name) ~~ 'B%')
and subtracting what cost_qual_eval says is the cost of the index
conditions. Normally that works all right, but here you have a very
expensive function that appears once in the filter and twice in the
indexquals, leading to a negative value for per-tuple CPU cost.

Even if we had only one indexqual derived from the filter condition,
we'd not be getting the right answer here, because actually the filter
condition *does* have to be evaluated at runtime, since it doesn't
exactly match the indexqual. I think this code probably dates to before
we had any notion of deriving simplified indexquals from special
filter conditions; it's not really right at all for such cases.

I think what we're going to need here is a real determination of exactly
which quals will actually have to be evaluated at runtime. The code is
trying to let that determination be postponed until createplan time, but
maybe we can't get away with that.

I'll see about fixing this for 9.2, but I doubt we'll consider
backpatching it. You should probably back off the cost assigned to the
noaccent function as a workaround.

regards, tom lane