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
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2002-11-12 16:34:02 | Re: bug? |
Previous Message | 2002-11-12 15:59:00 | how to configure postgresql |