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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Istvan Endredy | 2012-04-10 15:02:56 | Re: BUG #6579: negative cost in a planning |
Previous Message | Barry Bell | 2012-04-10 12:29:57 | Re: BUG #6534: Passing numeric Bind variables to ODBC driver convers to "Double precision" |