Re: Bad plan when join on function

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

2011/1/17 Zotov <zotov@xxxxxxxx>:
> It`s just a sample.
>
> select c.id from OneRow c join abstract a on a.id=AsInteger(c.id)
>
> "Nested Loop (cost=0.00..786642.96 rows=1 width=4) (actual
> time=91021.167..119601.344 rows=1 loops=1)"
> "Â Join Filter: ((a.id)::integer = asinteger((c.id)::integer))"
> " -> Seq Scan on onerow c (cost=0.00..1.01 rows=1 width=4) (actual
> time=0.007..0.008 rows=1 loops=1)"
> " -> Seq Scan on abstract a (cost=0.00..442339.78 rows=22953478 width=4)
> (actual time=0.003..115193.283 rows=22953478 loops=1)"
> "Total runtime: 119601.428 ms"
>
>
> select c.id from OneRow c join abstract a on a.id=c.id
>
> "Nested Loop (cost=0.00..13.85 rows=1 width=4) (actual
> time=254.579..254.585 rows=1 loops=1)"
> " -> Seq Scan on onerow c (cost=0.00..1.01 rows=1 width=4) (actual
> time=0.006..0.007 rows=1 loops=1)"
> " -> Index Scan using integ_1197 on abstract a (cost=0.00..12.83 rows=1
> width=4) (actual time=254.559..254.563 rows=1 loops=1)"
> "ÂÂÂÂÂÂÂ Index Cond: ((a.id)::integer = (c.id)::integer)"
> "Total runtime: 254.648 ms"
>
>
> OneRow Contains only one row,
> abstract contains 22 953 500 rows
>
> AsInteger is simple function on Delphi
> it just return input value
>
> CREATE OR REPLACE FUNCTION asinteger(integer)
> Â RETURNS integer AS
> 'oeudfpg.dll', 'AsInteger'
> Â LANGUAGE c VOLATILE
> Â COST 1;

are you sure so your function needs a VOLATILE flag?

Regards

Pavel Stehule

>
>
> Why SeqScan???
>
> this query is simple sample to show SLOW seq scan plan
> I have a real query what i don`t know when it will be done... but at
> firebird this query with full fetch 1-2 minutes
> I can`t give you this real query and database (database size is more, than
> 20 GB)
> as i see that query have same problem as this sample
> It`s so sad, because I spend so much time to support posgtresql in my
> project and now i see what more queries is slower more than 10 times...
> Please HELP!
>
> PostgreSQL version 9.0.2
>
> --
> Ð ÑÐÐÐÐÐÐÐÐ,
> ÐÐÑÐÐ ÐÐÐÐÐ ÐÐÐÐÐÐÐÑÐÐÐÑ
> ÑÑÐÐÐÐÐÐÑÐÐÑ ÐÑÐÐÐÐ ÐÐÑÑÑÑÐÐÐÑÐÑÐÑ
> ÐÐÐ "ÐÐÐ ÐÐÐÑÑÐÑÑÐÐÑ"
> Ð.ÐÐÐÐÐÐÐ, ÑÐ. ÐÐÐÐÑÑÐÐÑ, Ð. 10
> ÑÐÐ./ÑÐÐÑ: (4932) 41-01-21
> mailto: zotov@xxxxxxxx

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance