From: Zotov <zotov@oe-it.ru>
To: pgsql-performance@postgresql.org
Subject: Bad plan when join on function
Date: Mon, 17 Jan 2011 11:03:29 +0300
Message-ID: <4D33F7D1.4020201@oe-it.ru> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: zotov@oe-it.ru
Subject: Re: Bad plan when join on function
In-Reply-To: <4D33F7D1.4020201@oe-it.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox