Index Ignored Due To Use Of View

PostgreSQL 8.3.14
OS: Linux Redhat 5.4

Note: I have used the same subject for this email taken from an email: Posted 2011-02-24 13:29:22-08 by "David Johnston", because this seems to be a very similar observation.

Bug/Problem Summary:
We are using a simple query based on a simple view and the query optimizer is not choosing an index.
The same query without the view is using an index.
The same query on an almost identical view, but having either removed a single column which was generated via a function call or replace the function call with equivalent SQL, then the query optimizer is choosing an index.

Other notes:
We observe the same behaviour on two separate databases running on separate servers (both the same version).
The genetic query optimizer settings are all on defaults for these versions.

Details;
With the following view:

CREATE OR REPLACE VIEW vu_tbl_news_web AS
SELECT
n.id, n.id_cmpy, n.id_news, n.id_newshdline,
n.s_origcmpyname, n.s_hdline, n.s_news,
n.b_amend, n.b_replace,
n.dt_publish,
n.tsv_hdline, n.tsv_news,
n.b_hasorigdoc,
(SELECT h.s_hdline FROM tbl_newshdline h WHERE h.id = n.id_newshdline LIMIT 1) AS s_hdlinetype,
get_cmpyname(n.id_contrib) AS s_provider
FROM tbl_news n
WHERE n.dt_publish IS NOT NULL;

Query:
SELECT n.id, n.id_cmpy, n.s_origcmpyname,
n.s_hdline, n.dt_publish
FROM vu_tbl_news_web n
ORDER BY n.dt_publish DESC
LIMIT 25

Analyse produces:
Limit (cost=180017.37..180017.43 rows=25 width=80)
-> Sort (cost=180017.37..180110.54 rows=37267 width=80)
Sort Key: n.dt_publish
-> Subquery Scan n (cost=0.00..178965.72 rows=37267 width=80)
-> Seq Scan on tbl_news n (cost=0.00..178593.05 rows=37267 width=1152)
Filter: (dt_publish IS NOT NULL)
SubPlan
-> Limit (cost=0.00..4.29 rows=1 width=22)
-> Seq Scan on tbl_newshdline h (cost=0.00..4.29 rows=1 width=22)
Filter: (id = $0)

Although the number of rows (37k) is small, there is a lot of data in some of the columns so, with a seq. scan its taking nearly 2 seconds. Compared to 16ms when using an index.

Now if I perform the same query without using the view, such as:

SELECT n.id, n.id_cmpy, n.s_origcmpyname,
n.s_hdline, n.dt_publish
FROM tbl_news n
WHERE n.dt_publish IS NOT NULL
ORDER BY n.dt_publish DESC
LIMIT 25

Analyse produces:
Limit (cost=0.00..21.34 rows=25 width=73)
-> Index Scan Backward using tbl_news_publish1_key on tbl_news n (cost=0.00..31807.05 rows=37267 width=73)

Finally I discovered that if I remove a column from the view, that is generated via a function, then all works as expected?

CREATE OR REPLACE VIEW vu_tbl_news_web3 AS
SELECT
n.id, n.id_cmpy, n.id_news, n.id_newshdline,
n.s_origcmpyname, n.s_hdline, n.s_news,
n.b_amend, n.b_replace,
n.dt_publish, n.tsv_hdline, n.tsv_news,
n.b_hasorigdoc,
(SELECT h.s_hdline FROM tbl_newshdline h WHERE h.id = n.id_newshdline LIMIT 1) AS s_hdlinetype
FROM tbl_news n
WHERE n.dt_publish IS NOT NULL;

SELECT n.id, n.id_cmpy, n.s_origcmpyname,
n.s_hdline, n.dt_publish
FROM vu_tbl_news_web3 n
ORDER BY n.dt_publish DESC
LIMIT 25

Analyse produces:
Limit (cost=0.00..21.34 rows=25 width=73)
-> Index Scan Backward using tbl_news_publish1_key on tbl_news n (cost=0.00..31807.05 rows=37267 width=73)

The definition of the function used is:
CREATE OR REPLACE FUNCTION get_cmpyname(integer) RETURNS citext AS
$BODY$
DECLARE
idcmpy ALIAS FOR $1;
sumb citext;
sres citext;
BEGIN
SELECT INTO sumb, sres s_umbname, s_name FROM tbl_cmpy WHERE (id = idcmpy) LIMIT 1;
IF length(sumb) > 0 THEN
sres := sumb || ' - ' || sres;
END IF;
RETURN sres;
END $BODY$
LANGUAGE 'plpgsql' VOLATILE STRICT SECURITY DEFINER COST 100;

I also noted that if I replace the function call in the view with equivalent SQL:
(SELECT (CASE WHEN length(c.s_umbname) > 0 THEN c.s_umbname || ' - '::citext || c.s_name ELSE c.s_name END) AS s_cmpyname FROM tbl_cmpy c WHERE c.id = n.id_contrib LIMIT 1) AS s_provider
the problem also goes away.

Regards
Donald Fraser