Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id DC15B133740B
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 17 Jan 2011 04:03:47 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 71202-03
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 17 Jan 2011 08:03:40 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from oe-it.ru (oe-it.ru [109.60.135.184])
	by mail.postgresql.org (Postfix) with ESMTP id 57B591337B54
	for <pgsql-performance@postgresql.org>;
	Mon, 17 Jan 2011 04:03:39 -0400 (AST)
Received: from [192.168.114.217] (unknown [192.168.114.217])
	by oe-it.ru (Postfix) with ESMTP id 3D65DE01AAD
	for <pgsql-performance@postgresql.org>;
	Mon, 17 Jan 2011 11:03:36 +0300 (MSK)
Message-ID: <4D33F7D1.4020201@oe-it.ru>
Date: Mon, 17 Jan 2011 11:03:29 +0300
From: Zotov <zotov@oe-it.ru>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; ru;
	rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Bad plan when join on function
Content-Type: multipart/alternative;
	boundary="------------050705060507050407030000"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=2.261 tagged_above=-10 required=5
	tests=BAYES_20=-0.001, FSL_RU_URL=2.271, HTML_MESSAGE=0.001,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: **
X-Archive-Number: 201101/134
X-Sequence-Number: 41921

This is a multi-part message in MIME format.
--------------050705060507050407030000
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

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


--------------050705060507050407030000
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  </head>
  <body bgcolor="#ffffff" text="#000000">
    It`s just a sample.<br>
    <br>
    <font color="#330099">select c.id from OneRow c join abstract a on
      a.id=AsInteger(c.id)</font><br>
    <br>
    "Nested Loop  (cost=0.00..786642.96 rows=1 width=4) (actual
    time=91021.167..119601.344 rows=1 loops=1)"<br>
    "  Join Filter: ((a.id)::integer = asinteger((c.id)::integer))"<br>
    "  -&gt;  Seq Scan on onerow c  (cost=0.00..1.01 rows=1 width=4)
    (actual time=0.007..0.008 rows=1 loops=1)"<br>
    "  -&gt;  Seq Scan on abstract a  (cost=0.00..442339.78
    rows=22953478 width=4) (actual time=0.003..115193.283 rows=22953478
    loops=1)"<br>
    "Total runtime: 119601.428 ms"<br>
    <br>
    <br>
    <font color="#330099">select c.id from OneRow c join abstract a on
      a.id=c.id</font><br>
    <br>
    "Nested Loop  (cost=0.00..13.85 rows=1 width=4) (actual
    time=254.579..254.585 rows=1 loops=1)"<br>
    "  -&gt;  Seq Scan on onerow c  (cost=0.00..1.01 rows=1 width=4)
    (actual time=0.006..0.007 rows=1 loops=1)"<br>
    "  -&gt;  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)"<br>
    "        Index Cond: ((a.id)::integer = (c.id)::integer)"<br>
    "Total runtime: 254.648 ms"<br>
    <br>
    <br>
    OneRow Contains only one row,<br>
    abstract contains 22 953 500 rows<br>
    <br>
    AsInteger is simple function on Delphi<br>
    it just return input value<br>
    <br>
    CREATE OR REPLACE FUNCTION asinteger(integer)<br>
      RETURNS integer AS<br>
    'oeudfpg.dll', 'AsInteger'<br>
      LANGUAGE c VOLATILE<br>
      COST 1;<br>
    <br>
    <br>
    Why SeqScan???<br>
    <br>
    this query is simple sample to show SLOW seq scan plan <br>
    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<br>
    I can`t give you this real query and database (database size is
    more, than 20 GB) <br>
    as i see that query have same problem as this sample<br>
    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...<br>
    Please HELP!<br>
    <br>
    PostgreSQL version 9.0.2<br>
    <br>
    <pre class="moz-signature" cols="72">-- 
С уважением,
Зотов Роман Владимирович
руководитель Отдела инструментария 
ЗАО "НПО Консультант"
г.Иваново, ул. Палехская, д. 10
тел./факс: (4932) 41-01-21
mailto: <a class="moz-txt-link-abbreviated" href="mailto:zotov@oe-it.ru">zotov@oe-it.ru</a></pre>
  </body>
</html>

--------------050705060507050407030000--