From zotov@oe-it.ru Mon Jan  17 08:03:47 2011
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--



From pavel.stehule@gmail.com Mon Jan  17 20:13:20 2011
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 751E3133740B
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 17 Jan 2011 16:13:20 -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 87053-02
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 17 Jan 2011 20:13:13 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-iw0-f174.google.com (mail-iw0-f174.google.com
	[209.85.214.174])
	by mail.postgresql.org (Postfix) with ESMTP id BA4D013364DE
	for <pgsql-performance@postgresql.org>;
	Mon, 17 Jan 2011 16:13:12 -0400 (AST)
Received: by iwn9 with SMTP id 9so5026626iwn.19
	for <pgsql-performance@postgresql.org>;
	Mon, 17 Jan 2011 12:13:12 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:in-reply-to:references:from:date
	:message-id:subject:to:cc:content-type:content-transfer-encoding;
	bh=Xux64beLQKDM1UXNPqO9lu9xDYI8hOqTea8uMKYS9zo=;
	b=F4E+KVVL+jdCXNvw4TQ3w4k7gRzYdJHe6ydOnJ1BizOKvFnipGp3kRI7ekp/UE2P0q
	j6bBjOZE7NVGXvBPVF1QoHaqAlxB7aakuK7lNAfFWMK7gpcKljsjcVHyOEnMIdXz+xz2
	frLk6GM9XGQrUrfNSFbBEPFZk/QaZga4MpEKo=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:from:date:message-id:subject:to
	:cc:content-type:content-transfer-encoding;
	b=DUMuI+iNRdFT/LrcPlJM8pY+Zo3MlEsoS9wfLxHqVNjSFT1LPCQgYoSVJzmD2h+deG
	Vc7qnKI2zyf+QY1o3FKbZFrE3wnsGplgFqDD9WMdCiloCXoiYZ+WFNxjKo5Wu+DIfyOI
	Nwlx12bavMwOODPHotS4VM4Jgh/78I8W7DQ4s=
Received: by 10.42.179.9 with SMTP id bo9mr5026968icb.429.1295295192526; Mon,
	17 Jan 2011 12:13:12 -0800 (PST)
MIME-Version: 1.0
Received: by 10.42.172.193 with HTTP; Mon, 17 Jan 2011 12:12:31 -0800 (PST)
In-Reply-To: <4D33F7D1.4020201@oe-it.ru>
References: <4D33F7D1.4020201@oe-it.ru>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Mon, 17 Jan 2011 21:12:31 +0100
Message-ID: <AANLkTin9AJ1Y8pyO1vwrbB1Hjw+dteY0776ZVGnqK9T4@mail.gmail.com>
Subject: Re: Bad plan when join on function
To: Zotov <zotov@oe-it.ru>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.328 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, FSL_RU_URL=2.271,
	RCVD_IN_DNSWL_LOW=-0.7
X-Spam-Level: 
X-Archive-Number: 201101/135
X-Sequence-Number: 41922

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=3DAsInteger(c.id)
>
> "Nested Loop=C2=A0 (cost=3D0.00..786642.96 rows=3D1 width=3D4) (actual
> time=3D91021.167..119601.344 rows=3D1 loops=3D1)"
> "=C2=A0 Join Filter: ((a.id)::integer =3D asinteger((c.id)::integer))"
> "=C2=A0 ->=C2=A0 Seq Scan on onerow c=C2=A0 (cost=3D0.00..1.01 rows=3D1 w=
idth=3D4) (actual
> time=3D0.007..0.008 rows=3D1 loops=3D1)"
> "=C2=A0 ->=C2=A0 Seq Scan on abstract a=C2=A0 (cost=3D0.00..442339.78 row=
s=3D22953478 width=3D4)
> (actual time=3D0.003..115193.283 rows=3D22953478 loops=3D1)"
> "Total runtime: 119601.428 ms"
>
>
> select c.id from OneRow c join abstract a on a.id=3Dc.id
>
> "Nested Loop=C2=A0 (cost=3D0.00..13.85 rows=3D1 width=3D4) (actual
> time=3D254.579..254.585 rows=3D1 loops=3D1)"
> "=C2=A0 ->=C2=A0 Seq Scan on onerow c=C2=A0 (cost=3D0.00..1.01 rows=3D1 w=
idth=3D4) (actual
> time=3D0.006..0.007 rows=3D1 loops=3D1)"
> "=C2=A0 ->=C2=A0 Index Scan using integ_1197 on abstract a=C2=A0 (cost=3D=
0.00..12.83 rows=3D1
> width=3D4) (actual time=3D254.559..254.563 rows=3D1 loops=3D1)"
> "=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Index Cond: ((a.id)::integer =
=3D (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)
> =C2=A0 RETURNS integer AS
> 'oeudfpg.dll', 'AsInteger'
> =C2=A0 LANGUAGE c VOLATILE
> =C2=A0 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, tha=
n
> 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
>
> --
> =D0=A1 =D1=83=D0=B2=D0=B0=D0=B6=D0=B5=D0=BD=D0=B8=D0=B5=D0=BC,
> =D0=97=D0=BE=D1=82=D0=BE=D0=B2 =D0=A0=D0=BE=D0=BC=D0=B0=D0=BD =D0=92=D0=
=BB=D0=B0=D0=B4=D0=B8=D0=BC=D0=B8=D1=80=D0=BE=D0=B2=D0=B8=D1=87
> =D1=80=D1=83=D0=BA=D0=BE=D0=B2=D0=BE=D0=B4=D0=B8=D1=82=D0=B5=D0=BB=D1=8C =
=D0=9E=D1=82=D0=B4=D0=B5=D0=BB=D0=B0 =D0=B8=D0=BD=D1=81=D1=82=D1=80=D1=83=
=D0=BC=D0=B5=D0=BD=D1=82=D0=B0=D1=80=D0=B8=D1=8F
> =D0=97=D0=90=D0=9E "=D0=9D=D0=9F=D0=9E =D0=9A=D0=BE=D0=BD=D1=81=D1=83=D0=
=BB=D1=8C=D1=82=D0=B0=D0=BD=D1=82"
> =D0=B3.=D0=98=D0=B2=D0=B0=D0=BD=D0=BE=D0=B2=D0=BE, =D1=83=D0=BB. =D0=9F=
=D0=B0=D0=BB=D0=B5=D1=85=D1=81=D0=BA=D0=B0=D1=8F, =D0=B4. 10
> =D1=82=D0=B5=D0=BB./=D1=84=D0=B0=D0=BA=D1=81: (4932) 41-01-21
> mailto: zotov@oe-it.ru



From Kevin.Grittner@wicourts.gov Mon Jan  17 20:51:41 2011
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 5F6571337B9E
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 17 Jan 2011 16:51:41 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 61407-06
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 17 Jan 2011 20:51:34 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99])
	by mail.postgresql.org (Postfix) with ESMTP id C0CC6133740B
	for <pgsql-performance@postgresql.org>;
	Mon, 17 Jan 2011 16:51:33 -0400 (AST)
Received: from Courts-MTA by gw.wicourts.gov
	with Novell_GroupWise; Mon, 17 Jan 2011 14:51:32 -0600
Message-Id: <4D34576C02000025000396CB@gw.wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 8.0.1 
Date: Mon, 17 Jan 2011 14:51:24 -0600
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: <zotov@oe-it.ru>,<pgsql-performance@postgresql.org>
Subject: Re: Bad plan when join on function
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-4.21 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	RCVD_IN_DNSWL_MED=-2.3, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201101/137
X-Sequence-Number: 41924

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



From pavel.stehule@gmail.com Mon Jan  17 21:25:27 2011
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 8EA931337BD5
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 17 Jan 2011 17:25:27 -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 05258-08
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 17 Jan 2011 21:25:20 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-iw0-f174.google.com (mail-iw0-f174.google.com
	[209.85.214.174])
	by mail.postgresql.org (Postfix) with ESMTP id 0C74A1337B81
	for <pgsql-performance@postgresql.org>;
	Mon, 17 Jan 2011 17:25:13 -0400 (AST)
Received: by iwn9 with SMTP id 9so5086421iwn.19
	for <pgsql-performance@postgresql.org>;
	Mon, 17 Jan 2011 13:25:13 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:in-reply-to:references:from:date
	:message-id:subject:to:cc:content-type:content-transfer-encoding;
	bh=wVe6i5QTMOJQZ1GqYOMvE+WLrrA7DUiXbB2eWp6SD2o=;
	b=fXbuxRrMJFJo/s0X7vxgPRjwmI0qCXezjo4bsTsUjMxz4f63lIK/OsDlWO5q6uRTp2
	yZEgyV0N74UaAUWQ6IGasy5Vs6TAvRuIm5v/I7DPVxj/fI63EF/+rfwOxoZwXasRh8Er
	c1KCDFh6BsWpAUxEpvnVHVnGC5/v3k5v3STO4=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:from:date:message-id:subject:to
	:cc:content-type:content-transfer-encoding;
	b=ekQUU0PPmAFRU9+3TM29HZ1pr2Ka9Tt2Z8LWqUtM7twpDOS5rwIFFlN+rj/ZNMjbSD
	FjbZGYWtm3vd7iRcsEkeZvf5by5WK6EFHs5x7rz28vqd3mXBZCfvzTe9YMhpipyKJXms
	Y4kSCyErSdIN+3caec2wbPzHYuxtyhLYW5MIA=
Received: by 10.42.179.9 with SMTP id bo9mr5126399icb.429.1295299513699; Mon,
	17 Jan 2011 13:25:13 -0800 (PST)
MIME-Version: 1.0
Received: by 10.42.172.193 with HTTP; Mon, 17 Jan 2011 13:24:33 -0800 (PST)
In-Reply-To: <4D34576C02000025000396CB@gw.wicourts.gov>
References: <4D34576C02000025000396CB@gw.wicourts.gov>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Mon, 17 Jan 2011 22:24:33 +0100
Message-ID: <AANLkTi=527XTtWUfgXQKwG8AGENxyKUvJHRxYgSje39W@mail.gmail.com>
Subject: Re: Bad plan when join on function
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: zotov@oe-it.ru, pgsql-performance@postgresql.org
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, RCVD_IN_DNSWL_LOW=-0.7
X-Spam-Level: 
X-Archive-Number: 201101/140
X-Sequence-Number: 41927

2011/1/17 Kevin Grittner <Kevin.Grittner@wicourts.gov>:
> Zotov =C2=A0wrote:
>
>> select c.id from OneRow c join abstract a on a.id=3DAsInteger(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=3D# explain select 1 from a join b on a.f =3D sin(b.f);
                                 QUERY PLAN
---------------------------------------------------------------------------=
--
 Merge Join  (cost=3D809.39..1352.64 rows=3D10000 width=3D0)
   Merge Cond: (a.f =3D (sin(b.f)))
   ->  Index Scan using a_f_idx on a  (cost=3D0.00..318.25 rows=3D10000 wid=
th=3D8)
   ->  Sort  (cost=3D809.39..834.39 rows=3D10000 width=3D8)
         Sort Key: (sin(b.f))
         ->  Seq Scan on b  (cost=3D0.00..145.00 rows=3D10000 width=3D8)
(6 rows)

but functional index always helps

postgres=3D# create index on b((sin(f)));
CREATE INDEX
postgres=3D# explain select 1 from a join b on a.f =3D sin(b.f);
                                  QUERY PLAN
---------------------------------------------------------------------------=
----
 Merge Join  (cost=3D0.00..968.50 rows=3D10000 width=3D0)
   Merge Cond: (a.f =3D sin(b.f))
   ->  Index Scan using a_f_idx on a  (cost=3D0.00..318.25 rows=3D10000 wid=
th=3D8)
   ->  Index Scan using b_sin_idx on b  (cost=3D0.00..450.25 rows=3D10000 w=
idth=3D8)
(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
>



From tgl@sss.pgh.pa.us Mon Jan  17 21:33:55 2011
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id EADF71337B3E
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 17 Jan 2011 17:33:55 -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 08783-07
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 17 Jan 2011 21:33:49 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by mail.postgresql.org (Postfix) with ESMTP id 63D69133616B
	for <pgsql-performance@postgresql.org>;
	Mon, 17 Jan 2011 17:33:48 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id p0HLXjbw021548;
	Mon, 17 Jan 2011 16:33:45 -0500 (EST)
To: Pavel Stehule <pavel.stehule@gmail.com>
cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>, zotov@oe-it.ru,
	pgsql-performance@postgresql.org
Subject: Re: Bad plan when join on function 
In-reply-to: <AANLkTi=527XTtWUfgXQKwG8AGENxyKUvJHRxYgSje39W@mail.gmail.com> 
References: <4D34576C02000025000396CB@gw.wicourts.gov>
	<AANLkTi=527XTtWUfgXQKwG8AGENxyKUvJHRxYgSje39W@mail.gmail.com>
Comments: In-reply-to Pavel Stehule <pavel.stehule@gmail.com>
	message dated "Mon, 17 Jan 2011 22:24:33 +0100"
Date: Mon, 17 Jan 2011 16:33:44 -0500
Message-ID: <21547.1295300024@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201101/141
X-Sequence-Number: 41928

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



From pavel.stehule@gmail.com Mon Jan  17 21:37:48 2011
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id EDDE41336971
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 17 Jan 2011 17:37:48 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 24944-04
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 17 Jan 2011 21:37:42 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-iw0-f174.google.com (mail-iw0-f174.google.com
	[209.85.214.174])
	by mail.postgresql.org (Postfix) with ESMTP id AF164133616B
	for <pgsql-performance@postgresql.org>;
	Mon, 17 Jan 2011 17:37:41 -0400 (AST)
Received: by iwn9 with SMTP id 9so5096080iwn.19
	for <pgsql-performance@postgresql.org>;
	Mon, 17 Jan 2011 13:37:41 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:in-reply-to:references:from:date
	:message-id:subject:to:cc:content-type:content-transfer-encoding;
	bh=6s72AYz5fcVcSA+UwXdBJlxav/4M7zdUYMNrrXr4Ioo=;
	b=LHwtx0rQRJ4olzRoGZkrB1M0RxTYrL+rBcl/8YcijurRFUyJr+lxbSEWIH2b1d39rf
	rhDddQGUL0XYVOi4rIZyilsYkhA8QGYqfEVhNWivF9Od2B1cK1huKkPt5fDx6X/ScDp6
	x5fb2yYNojXxVXKRp2QRkSQ9rO6WrQjFP8kbw=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:from:date:message-id:subject:to
	:cc:content-type:content-transfer-encoding;
	b=iLKUOPcPYMnSiOgZfTSwG5yQPbRj5MKN3+8PDxPfKlmyiRRCx15dXjqQuOSHXofREp
	9QOHdvlvqoRRrjoJ+WF8oruNabojVwLIE4rFX/NwtDBQLJ24llWfm1gnFeXHl63p85it
	VzJ2sv4wy9Y1QPdInpdJx95do32V1Hm2BQarc=
Received: by 10.42.175.6 with SMTP id ay6mr5181715icb.94.1295300261556; Mon,
	17 Jan 2011 13:37:41 -0800 (PST)
MIME-Version: 1.0
Received: by 10.42.172.193 with HTTP; Mon, 17 Jan 2011 13:37:01 -0800 (PST)
In-Reply-To: <21547.1295300024@sss.pgh.pa.us>
References: <4D34576C02000025000396CB@gw.wicourts.gov>
	<AANLkTi=527XTtWUfgXQKwG8AGENxyKUvJHRxYgSje39W@mail.gmail.com>
	<21547.1295300024@sss.pgh.pa.us>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Mon, 17 Jan 2011 22:37:01 +0100
Message-ID: <AANLkTim33Of+Xy+wRMBGx=HZOi_8VFqLXrXyE+5qrJqv@mail.gmail.com>
Subject: Re: Bad plan when join on function
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>, zotov@oe-it.ru,
	pgsql-performance@postgresql.org
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, RCVD_IN_DNSWL_LOW=-0.7
X-Spam-Level: 
X-Archive-Number: 201101/142
X-Sequence-Number: 41929

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 effectiv=
ity
>
> As long as the function is VOLATILE, the planner can't use any
> intelligent query plan. =C2=A0Merge 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

>
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0regards, tom lane
>



From sthomas@peak6.com Tue Jan  18 14:15:31 2011
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 54DA013364DE
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue, 18 Jan 2011 10:15:31 -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 13116-07
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue, 18 Jan 2011 14:15:25 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from smtp2.peak6.com (smtp2.peak6.com [38.98.137.9])
	by mail.postgresql.org (Postfix) with ESMTP id 12A221337B80
	for <pgsql-performance@postgresql.org>;
	Tue, 18 Jan 2011 10:15:22 -0400 (AST)
X-AuditID: 0a02cb1e-b7bf8ae00000096d-84-4d35a0793468
Message-ID: <4D35A079.1040401@peak6.com>
Date: Tue, 18 Jan 2011 08:15:21 -0600
From: Shaun Thomas <sthomas@peak6.com>
Reply-To: sthomas@peak6.com
Organization: Peak6
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.7) Gecko/20100720 Lightning/1.0b2 Thunderbird/3.1.1
MIME-Version: 1.0
To: Zotov <zotov@oe-it.ru>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Bad plan when join on function
References: <4D33F7D1.4020201@oe-it.ru>
In-Reply-To: <4D33F7D1.4020201@oe-it.ru>
Content-Type: text/plain; charset="UTF-8"; format=flowed
content-transfer-encoding: 7bit
X-Brightmail-Tracker: AAAAAQAAAZE=
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201101/148
X-Sequence-Number: 41935

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