Bad plan when join on function

От: Zotov
Тема: Bad plan when join on function
Дата: ,
Msg-id: 4D33F7D1.4020201@oe-it.ru
(см: обсуждение, исходный текст)
Ответы: Re: Bad plan when join on function  (Pavel Stehule)
Re: Bad plan when join on function  (Shaun Thomas)
Список: pgsql-performance

Скрыть дерево обсуждения

Bad plan when join on function  (Zotov, )
 Re: Bad plan when join on function  (Pavel Stehule, )
 Re: Bad plan when join on function  ("Kevin Grittner", )
  Re: Bad plan when join on function  (Pavel Stehule, )
   Re: Bad plan when join on function  (Tom Lane, )
    Re: Bad plan when join on function  (Pavel Stehule, )
 Re: Bad plan when join on function  (Shaun Thomas, )

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: 

В списке pgsql-performance по дате сообщения:

От: Fernando Hevia
Дата:
Сообщение: Re: Problem with query
От: Achilleas Mantzios
Дата:
Сообщение: Re: "NOT IN" substantially slower in 9.0.2 than 8.3.13 - NOT EXISTS runs fast in both 8.3.13 and 9.0.2