Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jLkDe-0006CR-6P for pgsql-performance@arkaria.postgresql.org; Tue, 07 Apr 2020 09:08:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jLkDc-0008JN-Vq for pgsql-performance@arkaria.postgresql.org; Tue, 07 Apr 2020 09:08:28 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jLkDc-0008HT-Dx for pgsql-performance@lists.postgresql.org; Tue, 07 Apr 2020 09:08:28 +0000 Received: from mx07-00157001.pphosted.com ([62.209.51.8] helo=mx08-00157001.pphosted.com) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jLkDY-0001rQ-2y for pgsql-performance@postgresql.org; Tue, 07 Apr 2020 09:08:26 +0000 Received: from pps.filterd (m0042436.ppops.net [127.0.0.1]) by mx07-00157001.pphosted.com (8.16.0.42/8.16.0.42) with SMTP id 03792diC019773; Tue, 7 Apr 2020 11:08:21 +0200 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=temenos.com; h=from : to : cc : subject : date : message-id : references : in-reply-to : content-type : mime-version; s=20190904; bh=heBo8nH7iPHxV/aqhoLY27MNWCRc/4H5fyygrrOI6EU=; b=hkTLQhi3xc/g2Aq+sH643L4VQe29cMlmL1FrFu1CW+71LCTj+Wh7vq+tOUjO/5u7XVUr yJ/N86E72b/1OdFQSaxAzV/k0ZTOl9mL856kD7h/P5JeBi2zInUOTr0qlLpVj5ldL3S0 75K1TKGDooI7Ubjc6AsHYGZYtMnajCoSmBnlefFf9nRF8/OkKLElJpnkGw6RmTBXZ/Gp cPhwuoxhlwbnBl1qge0kz5YsxfJfqWy+SQYNJpMZqGMGuii/f1Fd9szcr9H9fkWdtdMl UuU5d7X5kUvRb5ecxqE3YitfLLLkTg0OOgB3bpILQ2pjYM4Pr04nlfjX4fDfJA/YEAvS zA== Received: from hub.temenos.com ([212.203.116.81]) by mx07-00157001.pphosted.com with ESMTP id 306fuskn6b-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128 verify=NOT); Tue, 07 Apr 2020 11:08:21 +0200 Received: from GVAEXCHMB2.temenosgroup.com (10.42.4.222) by GVAEXCHMB4.temenosgroup.com (10.42.5.131) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256) id 15.1.1779.2; Tue, 7 Apr 2020 10:08:20 +0100 Received: from GVAEXCHMB4.temenosgroup.com (10.42.5.131) by GVAEXCHMB2.temenosgroup.com (10.42.4.222) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256_P256) id 15.1.1779.2; Tue, 7 Apr 2020 11:08:20 +0200 Received: from NAM11-DM6-obe.outbound.protection.outlook.com (104.47.57.173) by gvaexchmb4.temenos.com (10.42.5.131) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256) id 15.1.1779.2 via Frontend Transport; Tue, 7 Apr 2020 10:08:20 +0100 ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=V3a/87xTHFpeFECBO3MnztJiAzXb5CDgZnKKwJxxgaDuWsVzebnjwGlYQpMOVWRoaH3u+3LuNNWMYF8mnGnU3MCEgP6P+1SNTItma56qs6r82yV/UZE7UBhePRmV5I5eAUXOqpcUTdM8iOCs7gWMqbOaVQf46WQVyzbiQ6WGT1DMtVRIE/Zlk3a5YPxw6hKlSom8JPTSsAX8NwdMrG2h4/deLz3ydyTkyvhCvqAP7XnQbWwNZOoe1pNiwE8AI9kXAPsZx5AYCM3wHCa7Or+rHG8HWkgWrFNl5jwHsJDEleKb8sBTh3katytq+uaUYVoOTY/bvi8SJeh6uy/K2RwJ8Q== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector9901; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=heBo8nH7iPHxV/aqhoLY27MNWCRc/4H5fyygrrOI6EU=; b=cOnVHN5pk2NyvPGXRDZajO+ipLrTeU/6YHsjnS6gwQ1Ng+8xA11sPrq5iJUaeRRKp4HJdZOf6nvfUnfKlzD2Fg3JvSRz+b0Sl7Gf6pY2RWni+9r9zP3GcbVaoe5eh7WCgRKggt5Re3N+6Qm+mjCEYnU2EY7RLepwYiPlhT9nvkvfscAMJIuhRWsfxlOEI8fMrdYqkw1rMoVee9ciwDtTHtaRudvqsXjIFgHbawpiYOzKqxkQ1nyfURTd38utB3m+q2ACYG29dG44KDyHVqwwNDJIPywOIr/6Y9/V2Zl8/lTO0xWgh5mkCZ5aWtda/2o35Bj5pwlce5/8doTHZHUgtw== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=temenos.com; dmarc=pass action=none header.from=temenos.com; dkim=pass header.d=temenos.com; arc=none Received: from DM6PR04MB4923.namprd04.prod.outlook.com (2603:10b6:5:fc::31) by DM6PR04MB5402.namprd04.prod.outlook.com (2603:10b6:5:101::23) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.2878.15; Tue, 7 Apr 2020 09:08:13 +0000 Received: from DM6PR04MB4923.namprd04.prod.outlook.com ([fe80::194a:924b:d605:73e2]) by DM6PR04MB4923.namprd04.prod.outlook.com ([fe80::194a:924b:d605:73e2%7]) with mapi id 15.20.2878.018; Tue, 7 Apr 2020 09:08:07 +0000 From: Rick Vincent To: Tom Lane , Justin Pryzby CC: "pgsql-performance@postgresql.org" , Manoj Kumar , Herve Aubert Subject: RE: Postgres not using index on views Thread-Topic: Postgres not using index on views Thread-Index: AdYL8S4zGeBW2c7eSvmvBjDQFjJ7vQAqCM+AAABWboAAB05+sA== Date: Tue, 7 Apr 2020 09:08:06 +0000 Message-ID: References: <20200407045929.GJ2228@telsasoft.com> <26256.1586236149@sss.pgh.pa.us> In-Reply-To: <26256.1586236149@sss.pgh.pa.us> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [89.217.120.246] x-ms-publictraffictype: Email x-ms-office365-filtering-correlation-id: d0be0f2f-225f-4d6b-c911-08d7dad32fcb x-ms-traffictypediagnostic: DM6PR04MB5402: x-ms-exchange-transport-forked: True x-microsoft-antispam-prvs: x-ms-oob-tlc-oobclassifiers: OLM:10000; x-forefront-prvs: 036614DD9C x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:DM6PR04MB4923.namprd04.prod.outlook.com;PTR:;CAT:NONE;SFTY:;SFS:(10019020)(4636009)(39860400002)(396003)(346002)(366004)(136003)(376002)(4326008)(9686003)(107886003)(478600001)(66946007)(86362001)(26005)(2906002)(186003)(66574012)(55016002)(81166006)(7696005)(110136005)(52536014)(54906003)(76116006)(8676002)(81156014)(33656002)(6506007)(966005)(30864003)(66446008)(316002)(66476007)(64756008)(71200400001)(66556008)(8936002)(53546011)(5660300002);DIR:OUT;SFP:1102; received-spf: None (protection.outlook.com: temenos.com does not designate permitted sender hosts) x-ms-exchange-senderadcheck: 1 x-microsoft-antispam: BCL:0; x-microsoft-antispam-message-info: jouUTXajDvLtU2NJdVl6MNhCJWnZRq/a9WIMk4lcA6mieVT+/Mx4g2Iab/wNwHhNvzqwVsTBML7wZkjiazOT3VO5M1k2prX5HL2zDvjPYdSHjA0pR2iiXLqhWqq6M7X6jCIFK7V6WkO/m+ECVLYCIndln7Y2xlkiP6rk5Mg0nT2wF6AQ8UKx5r8dxXQR5ISKCAN7KookRSOhY3Y7w1ITxLNIMLlk1G7SZnfdoGu/RQLBIpxmS67a8QuvmMiiZGhrbCloMdTPx3kVOz0DChOAgvzwXZ36BBO8LMn42WzRPzq+fQZSsPzS2KTb2rJV6y0q08wYUoBersbltT0a9YiAsG5X7gsEBB2Icf8Q9PJyUzB9OvRjyNgVh5BPRMoi0DztVgOWcX1qXpmOinLLwdYXi8WTOSlsSC/t85H9/nCTo1Bm89OcsMzAB/FfqaT1JRed x-ms-exchange-antispam-messagedata: 5Jx3qtVo+0R5+C7JsPjvbozovUbJmnEP5J5Ug5HnR7SUup073jGeL3SOKiCwVidZt4cq0O5/OMfhR2FVH5KfE0l9cVKzrCYzBGRIw4KQa6dIvJR+EZgkDlyatqp4lfbghMtY+lVllvPp7LSyOt8SBw== Content-Type: multipart/alternative; boundary="_000_DM6PR04MB49231EBFFF7CFB2F18634326C1C30DM6PR04MB4923namp_" MIME-Version: 1.0 X-MS-Exchange-CrossTenant-Network-Message-Id: d0be0f2f-225f-4d6b-c911-08d7dad32fcb X-MS-Exchange-CrossTenant-originalarrivaltime: 07 Apr 2020 09:08:07.0004 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: d5d2540f-f60a-45ad-86a9-e2e792ee6669 X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: GrAi1b250x2SKCI/f0a5T4maN6u8bgPin3PylyL13tmAH4rF+NGSuMREq8fjIt8ciQulrSZK6CtKUa4hAAbfLQ== X-MS-Exchange-Transport-CrossTenantHeadersStamped: DM6PR04MB5402 X-OriginatorOrg: temenos.com X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10434:6.0.138,18.0.676 definitions=2020-04-07_01:2020-04-07,2020-04-07 signatures=0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_000_DM6PR04MB49231EBFFF7CFB2F18634326C1C30DM6PR04MB4923namp_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi Tom, The function is defined as below, so no use of VOLATILE. Let me know if yo= u need any other information. I am hoping the below will further clarify t= he issue. CREATE OR REPLACE FUNCTION extractValueJS (sVar text, nfm INTEGER, nvm INTE= GER) RETURNS VARCHAR as $$ declare sRet text :=3D ''; nSize int :=3D 0; retVal int :=3D 0; cVar text[] :=3D regexp_split_to_array(sVar,''); idx int :=3D 1; nStart int :=3D 0; nEnd int :=3D 0; begin etc... return sRet; end; $$ LANGUAGE plpgsql; After reading you link..... Here is a better explain plan: Explain on the table: EXPLAIN (analyze,BUFFERS) select RECID from "FBNK_CUSTOMER" Seq Scan on "FBNK_CUSTOMER" (cost=3D0.00..22.80 rows=3D180 width=3D7) (act= ual time=3D0.011..0.073 rows=3D180 loops=3D1) Buffers: shared hit=3D21 Planning Time: 0.056 ms Execution Time: 0.091 ms Explain on the view: EXPLAIN (analyze,BUFFERS) select RECID from "V_FBNK_CUSTOMER" Subquery Scan on "V_FBNK_CUSTOMER" (cost=3D0.00..19014.60 rows=3D180 width= =3D7) (actual time=3D455.727..76837.097 rows=3D180 loops=3D1) Buffers: shared hit=3D204 -> Seq Scan on "FBNK_CUSTOMER" a (cost=3D0.00..19012.80 rows=3D180 widt= h=3D14575) (actual time=3D455.726..76836.791 rows=3D180 loops=3D1) Buffers: shared hit=3D204 Planning Time: 1.109 ms Execution Time: 76838.505 ms Explain on view with a column: EXPLAIN (analyze,BUFFERS) SELECT RECID FROM "V_FBNK_CUSTOMER" WHERE "TESTER" =3D '5.00' ORDER BY REC= ID Sort (cost=3D19015.06..19015.06 rows=3D1 width=3D7) (actual time=3D76033.4= 75..76033.475 rows=3D1 loops=3D1) Sort Key: "V_FBNK_CUSTOMER".recid Sort Method: quicksort Memory: 25kB Buffers: shared hit=3D21 -> Subquery Scan on "V_FBNK_CUSTOMER" (cost=3D0.00..19015.05 rows=3D1 w= idth=3D7) (actual time=3D66521.952..76033.434 rows=3D1 loops=3D1) Filter: (("V_FBNK_CUSTOMER"."TESTER")::text =3D '5.00'::text) Rows Removed by Filter: 179 Buffers: shared hit=3D21 -> Seq Scan on "FBNK_CUSTOMER" a (cost=3D0.00..19012.80 rows=3D18= 0 width=3D14575) (actual time=3D462.949..76033.096 rows=3D180 loops=3D1) Buffers: shared hit=3D21 Planning Time: 0.819 ms Execution Time: 76033.731 ms But on the underlying table and not the view but just using the one view co= lumn called TESTER: EXPLAIN (analyze,BUFFERS) SELECT RECID FROM "FBNK_CUSTOMER" WHERE extractValueJS(XMLRECORD, 179, 9) = =3D '5.00' ORDER BY RECID Sort (cost=3D68.26..68.27 rows=3D1 width=3D7) (actual time=3D220.403..220.= 404 rows=3D1 loops=3D1) Sort Key: recid Sort Method: quicksort Memory: 25kB Buffers: shared hit=3D21 -> Seq Scan on "FBNK_CUSTOMER" (cost=3D0.00..68.25 rows=3D1 width=3D7) = (actual time=3D193.000..220.397 rows=3D1 loops=3D1) Filter: ((extractvaluejs((xmlrecord)::text, 179, 9))::text =3D '5.0= 0'::text) Rows Removed by Filter: 179 Buffers: shared hit=3D21 Planning Time: 0.045 ms Execution Time: 220.418 ms Other info: SELECT relname, relpages, reltuples, relallvisible, relkind, relnatts, relh= assubclass, reloptions, pg_table_size(oid) FROM pg_class WHERE relname=3D'F= BNK_CURRENCY'; relname relpages reltuples relallvisible relkind relnatts = relhassubclass reloptions pg_table_size FBNK_CURRENCY 6 93 0 r 2 false NULL 819= 20 Version is: PostgreSQL 11.7 (Debian 11.7-2.pgdg90+1) on x86_64-pc-linux-gnu, compiled b= y gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit It is a postgres docker image. Thanks, Rick -----Original Message----- From: Tom Lane Sent: Tuesday, April 7, 2020 7:09 AM To: Justin Pryzby Cc: Rick Vincent ; pgsql-performance@postgresql.org; = Manoj Kumar ; Herve Aubert Subject: Re: Postgres not using index on views Justin Pryzby > writes: > On Mon, Apr 06, 2020 at 02:19:59PM +0000, Rick Vincent wrote: >> The following query takes an extremely long time for only 180 rows, and = what this means is that we would have to index anything appearing in the wh= ere clause for every table in order to use views because the views seem not= to consider the select clause. Why is that and does anyone know a way aro= und this? > Is there a reason why you don't store the extracted value in its own colu= mn ? The planner seems to be quite well aware that the slower query is going to = be slower, since the estimated costs are much higher. Since it's not choos= ing to optimize into a faster form, I wonder whether it's constrained by se= mantic requirements. In particular, I'm suspicious that some of those func= tions you have in the view are marked "volatile", preventing them from bein= g optimized away. Beyond that guess, though, there's really not enough info here to say. The info we usually ask for to debug slow-query problems is explained at https://wiki.postgresql.org/wiki/Slow_Query_Questions regards, tom lane The information in this e-mail and any attachments is confidential and may = be legally privileged. It is intended solely for the addressee or addressee= s. Any use or disclosure of the contents of this e-mail/attachments by a no= t intended recipient is unauthorized and may be unlawful. If you have recei= ved this e-mail in error please notify the sender. Please note that any vie= ws or opinions presented in this e-mail are solely those of the author and = do not necessarily represent those of TEMENOS. We recommend that you check = this e-mail and any attachments against viruses. TEMENOS accepts no liabili= ty for any damage caused by any malicious code or virus transmitted by this= e-mail. --_000_DM6PR04MB49231EBFFF7CFB2F18634326C1C30DM6PR04MB4923namp_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
Hi Tom,
 
The function is defined as below, so no use of VOLATILE.  Let me = know if you need any other information.  I am hoping the below will fu= rther clarify the issue.
 
CREATE OR REPLACE FUNCTION extractValueJS (sVar text, nfm INTEGER, nvm= INTEGER)
RETURNS VARCHAR as $$
declare
sRet text :=3D '';
nSize int :=3D 0;
retVal int :=3D 0;
cVar text[] :=3D regexp_split_to_array(sVar,'');
idx int :=3D 1;
nStart int :=3D 0;
nEnd int :=3D 0;
begin
etc...
        return sRet;
end;
$$ LANGUAGE plpgsql;
 
After reading you link…..
 
Here is a better explain plan:
 
Explain on the table:
 
EXPLAIN (analyze,BUFFERS)
select RECID f= rom "FBNK_CUSTOMER"=
Seq Scan on "FBNK_CUSTOMER"  (cost=3D0.00..22.80 rows= =3D180 width=3D7) (actual time=3D0.011..0.073 rows=3D180 loops=3D1)
  Buffers: shared hit=3D21
Planning Time: 0.056 ms
Execution Time: 0.091 ms
 
Explain on the view:
 
EXPLAIN (analyze,BUFFERS)
select RECID f= rom "V_FBNK_CUSTOMER"
 
Subquery Scan on "V_FBNK_CUSTOMER"  (cost=3D0.00..19014= .60 rows=3D180 width=3D7) (actual time=3D455.727..76837.097 rows=3D180 loop= s=3D1)
  Buffers: shared hit=3D204
  ->  Seq Scan on "FBNK_CUSTOMER" a  (cost= =3D0.00..19012.80 rows=3D180 width=3D14575) (actual time=3D455.726..76836.7= 91 rows=3D180 loops=3D1)
        Buffers: shared hit=3D204
Planning Time: 1.109 ms
Execution Time: 76838.505 ms<= /span>
 
Explain on view with a column:
 
EXPLAIN (analyze,BUFFERS)
SELECT RECID F= ROM "V_FBNK_CUSTOMER" WHERE "TESTER&qu= ot; =3D '5.00' ORDER= BY RECID
Sort  (cost=3D19015.06..19015.06 rows=3D1 width=3D7) (actual time= =3D76033.475..76033.475 rows=3D1 loops=3D1)
  Sort Key: "V_FBNK_CUSTOMER".recid
  Sort Method: quicksort  Memory: 25kB
  Buffers: shared hit=3D21
  ->  Subquery Scan on "V_FBNK_CUSTOMER"  = (cost=3D0.00..19015.05 rows=3D1 width=3D7) (actual time=3D66521.952..76033.= 434 rows=3D1 loops=3D1)
        Filter: (("V_FBNK_CUST= OMER"."TESTER")::text =3D '5.00'::text)
        Rows Removed by Filter: 179=
        Buffers: shared hit=3D21
        ->  Seq Scan on &qu= ot;FBNK_CUSTOMER" a  (cost=3D0.00..19012.80 rows=3D180 width=3D14= 575) (actual time=3D462.949..76033.096 rows=3D180 loops=3D1)
           &nbs= p;  Buffers: shared hit=3D21
Planning Time: 0.819 ms
Execution Time: 76033.731 ms<= /span>
 
But on the underlying table and not the view but just using the one view column cal= led TESTER:
 
EXPLAIN (analyze,BUFFERS)
SELECT RECID F= ROM "FBNK_CUSTOMER" WHERE extractValueJS(XMLRECORD, 179, 9) =3D '5.00' ORDER BY= RECID
Sort  (cost=3D68.26..68.27 rows=3D1 width=3D7) (actual time=3D220= .403..220.404 rows=3D1 loops=3D1)
  Sort Key: recid
  Sort Method: quicksort  Memory: 25kB
  Buffers: shared hit=3D21
  ->  Seq Scan on "FBNK_CUSTOMER"  (cost= =3D0.00..68.25 rows=3D1 width=3D7) (actual time=3D193.000..220.397 rows=3D1= loops=3D1)
        Filter: ((extractvaluejs((x= mlrecord)::text, 179, 9))::text =3D '5.00'::text)
        Rows Removed by Filter: 179=
        Buffers: shared hit=3D21
Planning Time: 0.045 ms
Execution Time: 220.418 ms
 
Other info:
 
SELECT relname, = relpages, reltuples, relallvisible, relkind, relnatts, relhassubclass, reloptions, pg_table_size(oid) FROM pg_class WHERE r= elname=3D'FBNK_CURRENCY';
 
relname relpages        reltuples&n= bsp;      relallvisible   relkind relnat= ts        relhassubclass  reloption= s      pg_table_size
FBNK_CURRENCY   6       93&nbs= p;     0       r &nb= sp;     2       false&nbs= p;  NULL    81920
 
Version is:
PostgreSQL 11.7 (Debian 11.7-2.pgdg90+1) on x86_64-pc-linux-gnu, c= ompiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
 
It is a postgres docker image.
 
Thanks,
Rick
 
-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Tuesday, April 7, 2020 7:09 AM
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Rick Vincent <rvincent@temenos.com>; pgsql-performance@postgresql= .org; Manoj Kumar <manojkumar@temenos.com>; Herve Aubert <haubert@= temenos.com>
Subject: Re: Postgres not using index on views
 
Justin Pryzby <pryzby@telsa= soft.com> writes:
> On Mon, Apr 06, 2020 at 02:19:59PM +0000, Rick Vincent wrote:=
>> The following query takes an extremely long time for only 180= rows, and what this means is that we would have to index anything appearin= g in the where clause for every table in order to use views because the vie= ws seem not to consider the select clause.  Why is that and does anyone know a way around this?
 
> Is there a reason why you don't store the extracted value in its = own column ?
 
The planner seems to be quite well aware that the slower query is goin= g to be slower, since the estimated costs are much higher.  Since it's= not choosing to optimize into a faster form, I wonder whether it's constra= ined by semantic requirements.  In particular, I'm suspicious that some of those functions you have in the view are marked= "volatile", preventing them from being optimized away.
 
Beyond that guess, though, there's really not enough info here to say.=
The info we usually ask for to debug slow-query problems is explained = at
 
 
           &nbs= p;            regard= s, tom lane
 

T= he information in this e-mail and any attachments is confidential and may b= e legally privileged. It is intended solely for the addressee or addressees= . Any use or disclosure of the contents of this e-mail/attachments by a not intended recipient is unauthorized and = may be unlawful. If you have received this e-mail in error please notify th= e sender. Please note that any views or opinions presented in this e-mail a= re solely those of the author and do not necessarily represent those of TEMENOS. We recommend that you check = this e-mail and any attachments against viruses. TEMENOS accepts no liabili= ty for any damage caused by any malicious code or virus transmitted by this= e-mail.
--_000_DM6PR04MB49231EBFFF7CFB2F18634326C1C30DM6PR04MB4923namp_--