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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-bugs by date

  From Date Subject
Next Message Stephan Szabo 2002-11-12 16:34:02 Re: bug?
Previous Message roberto 2002-11-12 15:59:00 how to configure postgresql