Skip site navigation (1) Skip section navigation (2)

BUG #6579: negative cost in a planning

From: istvan(dot)endredy(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #6579: negative cost in a planning
Date: 2012-04-10 14:43:07
Message-ID: E1SHcHj-000451-IT@wrigleys.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
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


Responses

pgsql-bugs by date

Next:From: Istvan EndredyDate: 2012-04-10 15:02:56
Subject: Re: BUG #6579: negative cost in a planning
Previous:From: Barry BellDate: 2012-04-10 12:29:57
Subject: Re: BUG #6534: Passing numeric Bind variables to ODBC driver convers to "Double precision"

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group