Re: View not using index - Mailing list pgsql-sql
| From | Brice André |
|---|---|
| Subject | Re: View not using index |
| Date | |
| Msg-id | CAOBG12=eaYK5Zsgdu9Oes3s5jeQv8Feb9arMKBR6rk+pOERiNg@mail.gmail.com Whole thread Raw |
| In response to | View not using index (gmb <gmbouwer@gmail.com>) |
| Responses |
Re: View not using index
|
| List | pgsql-sql |
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@gmail.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql