Bad plan when join on function

From: Zotov <zotov(at)oe-it(dot)ru>
To: pgsql-performance(at)postgresql(dot)org
Subject: Bad plan when join on function
Date: 2011-01-17 08:03:29
Message-ID: 4D33F7D1.4020201@oe-it.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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(at)oe-it(dot)ru

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Jayadevan M 2011-01-17 08:44:05 Re: Possible to improve query plan?
Previous Message Jeremy Palmer 2011-01-17 05:13:25 Re: Possible to improve query plan?