Re: View not using index

Dear Gmb,

From what I know of the postgresql query planner, the choice of using an
index or not is based on stats collected on the table content. This implies
that :

- If your test DB has so few data that it is not efficient to use the
index, this last will not be used. You should probably try to insert more
data before performing the test
- the query planner uses table statistics to decide if it uses an index.
But, if those statistics are not up-to-date, the choice can be not optimal.
You should maybe try to look at 'Analyse' command to get more info :

http://www.postgresql.org/docs/9.1/static/sql-analyze.html

Hope this helps,

Brice

2015-09-15 13:56 GMT+02:00 gmb <gmbouwer(at)gmail(dot)com>:

> HI
>
> I hope somebody can give some guidance.
> Since our application make extensive use of views, this is becoming a
> concern for me.
>
> Please see below:
>
> CREATE TABLE detail ( invno VARCHAR, accno INTEGER, info INTEGER[] );
>
> CREATE OR REPLACE VIEW detailview AS
> ( SELECT invno , accno , COALESCE( info[1],0 ) info1, COALESCE( info[2],0 )
> info2, COALESCE( info[3],0 ) info3, COALESCE( info[4],0 ) info4 FROM detail
> );
>
> CREATE INDEX detail_ix_info3 ON detail ( ( info[3] ) ) WHERE COALESCE(
> info[3],0 ) = 1;
>
> EXPLAIN SELECT * FROM detail WHERE COALESCE( info[3],0 ) =1;
>
> QUERY PLAN
>
> ------------------------------------------------------------------------------
> Bitmap Heap Scan on detail (cost=4.13..12.59 rows=4 width=68)
> Recheck Cond: (COALESCE(info[3], 0) = 1)
> -> Bitmap Index Scan on detail_ix_info3 (cost=0.00..4.13 rows=4
> width=0)
> (3 rows)
>
> EXPLAIN SELECT * FROM detailview WHERE COALESCE( info3,0 ) =1;
>
> QUERY PLAN
> --------------------------------------------------------
> Seq Scan on detail (cost=0.00..20.38 rows=4 width=68)
> Filter: (COALESCE(COALESCE(info[3], 0), 0) = 1)
> (2 rows)
>
>
> This is an oversimplified example; the view in our production env provides
> for 20 elements in the info array column. My table in productions env
> contains ~10mil rows.
>
> Is there any way in which I can force the view to use the index?
>
>
> Regards
>
>
>
>
> --
> View this message in context:
> http://postgresql.nabble.com/View-not-using-index-tp5865953.html
> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>