3

Is there any way to force PostgreSQL to use an index that I created on a materialized view table? I tried every combination possible of moving the column attributes around to make Postgres to use my index...

I have this table:

=> explain select * from flight3mv1;
                         QUERY PLAN                          
-------------------------------------------------------------
 Seq Scan on flight3mv1  (cost=0.00..3.50 rows=150 width=26)
(1 row)

When I create my index on the materialized view, nothing changed on my query plan.

=> create index flight3mv1index on flight3mv1(c_nation,s_nation);
=> analyze flight3mv1;
=> explain select * from flight3mv1 where c_nation='CHINA' and s_nation='CHINA';

                                      QUERY PLAN                                       
---------------------------------------------------------------------------------------
 Seq Scan on flight3mv1  (cost=0.00..4.25 rows=6 width=26)
   Filter: (((c_nation)::text = 'CHINA'::text) AND ((s_nation)::text = 'CHINA'::text))
(2 rows)

Here is my flight3mv1 table:

=> \d flight3mv1
    Materialized view "public.flight3mv1"
  Column  |         Type          | Modifiers 
----------+-----------------------+-----------
 c_nation | character varying(15) | 
 s_nation | character varying(15) | 
 d_year   | integer               | 
 revenue  | bigint                | 
Indexes:
    "flight3mv1index" btree (c_nation, s_nation) CLUSTER
  • 3
    An index is not efficient for such small count of rows (150). So query planner does not used it. – Abelisto May 5 '16 at 6:03
  • 1
    Abelisto is right. For such a tiny table an index will never be used. – a_horse_with_no_name May 5 '16 at 8:15
  • That's what I thought. I tried to reload it with 200,000 rows, but I didn't get any improvement in performance. I guess that was still not enough. – the1 May 5 '16 at 13:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.