Обсуждение: Bad plan when join on function

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;


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@oe-it.ru

2011/1/17 Zotov <zotov@oe-it.ru>:
> 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@oe-it.ru

Zotov  wrote:

> select c.id from OneRow c join abstract a on a.id=AsInteger(c.id)

> Why SeqScan???

Because you don't have an index on AsInteger(c.id).

If your function is IMMUTABLE (each possible combination of input
values always yields the same result), and you declare it such, then
you can index on the function, and it will perform at a speed similar
to the other example.

-Kevin

2011/1/17 Kevin Grittner <Kevin.Grittner@wicourts.gov>:
> Zotov  wrote:
>
>> select c.id from OneRow c join abstract a on a.id=AsInteger(c.id)
>
>> Why SeqScan???
>
> Because you don't have an index on AsInteger(c.id).
>
> If your function is IMMUTABLE (each possible combination of input
> values always yields the same result), and you declare it such, then
> you can index on the function, and it will perform at a speed similar
> to the other example.

it should to work without functional index - but not sure about effectivity

postgres=# explain select 1 from a join b on a.f = sin(b.f);
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Merge Join  (cost=809.39..1352.64 rows=10000 width=0)
   Merge Cond: (a.f = (sin(b.f)))
   ->  Index Scan using a_f_idx on a  (cost=0.00..318.25 rows=10000 width=8)
   ->  Sort  (cost=809.39..834.39 rows=10000 width=8)
         Sort Key: (sin(b.f))
         ->  Seq Scan on b  (cost=0.00..145.00 rows=10000 width=8)
(6 rows)

but functional index always helps

postgres=# create index on b((sin(f)));
CREATE INDEX
postgres=# explain select 1 from a join b on a.f = sin(b.f);
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Merge Join  (cost=0.00..968.50 rows=10000 width=0)
   Merge Cond: (a.f = sin(b.f))
   ->  Index Scan using a_f_idx on a  (cost=0.00..318.25 rows=10000 width=8)
   ->  Index Scan using b_sin_idx on b  (cost=0.00..450.25 rows=10000 width=8)
(4 rows)

regards

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

Pavel Stehule <pavel.stehule@gmail.com> writes:
> it should to work without functional index - but not sure about effectivity

As long as the function is VOLATILE, the planner can't use any
intelligent query plan.  Merge or hash join both require at least
stable join keys.

            regards, tom lane

2011/1/17 Tom Lane <tgl@sss.pgh.pa.us>:
> Pavel Stehule <pavel.stehule@gmail.com> writes:
>> it should to work without functional index - but not sure about effectivity
>
> As long as the function is VOLATILE, the planner can't use any
> intelligent query plan.  Merge or hash join both require at least
> stable join keys.

sure, my first advice was a question about function volatility - and
my sentence was related to using immutable function.

regards

Pavel Stehule

>
>                        regards, tom lane
>

On 01/17/2011 02:03 AM, Zotov wrote:

> select c.id from OneRow c join abstract a on a.id=AsInteger(c.id)
>
> OneRow Contains only one row,
> abstract contains 22 953 500 rows
>
> AsInteger is simple function on Delphi
> it just return input value

Ok... there has to be some kind of misunderstanding, here. First of all,
if you're trying to cast a value to an integer, there are so many
built-in ways to do this, I can't imagine why you'd call a C function.
The most common for your example would be ::INT.

Second, you need to understand how the optimizer works. It doesn't know
what the function will return, so it has to apply the function to every
row in your 'abstract' table. You can get around this by applying an
index to your table with the result of your function, but to do that,
you'll have to mark your function as STABLE or IMMUTABLE instead of
VOLATILE.

Joining on the result of a function will always do this. The database
can't know what your function will return. If you can avoid using a
function in your join clause, do so.

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@peak6.com

______________________________________________

See  http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email