Skip site navigation (1) Skip section navigation (2)

bug?

From: "John Liu" <johnl(at)synthesys(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-bugs(at)postgresql(dot)org>
Subject: bug?
Date: 2002-11-12 15:59:27
Message-ID: NDBBKKKHILOHGHNKGOCEOEPAEFAA.johnl@synthesys.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Hi, Tom,
we're using version 7.2.

I've a document table, the cdi column is indexed.
I tried to do a query by using cdi in two ways -

1. the simple query as expected is using index scan
edbs=# explain select emrxid from document where cdi='1031-15402';
NOTICE:  QUERY PLAN:

Index Scan using document_cdi_key on document  (cost=0.00..4.43 rows=1
width=27)

2. the one using plsql function is not using index scan!
EXPLAIN
edbs=# explain select getEmrxid('1031-15402') from document;
NOTICE:  QUERY PLAN:

Seq Scan on document  (cost=0.00..447377.72 rows=13018272 width=0)

here's the simple fuction -
create FUNCTION getEmrxid(text)
RETURNS text AS'
DECLARE
      cdi_in ALIAS FOR $1;
      docrec document%ROWTYPE;
BEGIN
      select into docrec * from document where cdi = cdi_in;
RETURN docrec.emrxid;
END;
'LANGUAGE 'plpgsql';

This is not what I expected. If it's normal in pgsql, would you
providing some suggestions how to let function use index.

thanks in advance.

john


In response to

Responses

  • Re: bug? at 2002-11-12 16:34:02 from Stephan Szabo

pgsql-bugs by date

Next:From: Stephan SzaboDate: 2002-11-12 16:34:02
Subject: Re: bug?
Previous:From: robertoDate: 2002-11-12 15:59:00
Subject: how to configure postgresql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group