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

Index Ignored Due To Use Of View

From: "Donald Fraser" <postgres(at)kiwi-fraser(dot)net>
To: "[BUGS]" <pgsql-bugs(at)postgresql(dot)org>
Subject: Index Ignored Due To Use Of View
Date: 2011-03-23 12:50:19
Message-ID: 7C3EA8B4867547AC899AD3EC43DEA6AA@DEVELOP1 (view raw or flat)
Thread:
Lists: pgsql-bugs
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

pgsql-bugs by date

Next:From: Donald FraserDate: 2011-03-23 13:10:28
Subject: Re: Index Ignored Due To Use Of View
Previous:From: Alex LaiDate: 2011-03-23 12:07:43
Subject: Re: postgres 9 streaming replication

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