Optimizing PostgreSQL with Index I'm having a hard time trying to get PostgreSQL to use an index that I created. I understand that PostgreSQL is smart enough to determine when an index should be used because in most cases a seq scan is fine. However, I want to make my table to run faster. Can someone tell me what am I doing wrong with my index? My Query Plan time and cost does not change.

I know when I use the index in Oracle, it actually improves the performance. I'm not sure how I can get PostgreSQL to do the same.

This is my query:

select sum(lo_revenue), d_year, p_brand1 
from lineorder, dwdate, part, supplier 
where lo_orderdate = d_datekey 
    and lo_partkey = p_partkey 
    and lo_suppkey = s_suppkey 
    and p_brand1 between 'MFGR#2221' and 'MFGR#2228' 
    and s_region = 'ASIA' 
    group by d_year, p_brand1 
    order by d_year, p_brand1;


This is my index:

create index index1 on lineorder(lo_revenue,lo_orderdate,lo_partkey,lo_suppkey);


Here is what I get when I run an EXPLAIN:

QUERY PLAN                                

-------------------------------------------------------------------------------------------------------
------------------------------
 Sort  (cost=166357.23..166357.26 rows=13 width=17)
   Sort Key: dwdate.d_year, part.p_brand1
   -&gt;  HashAggregate  (cost=166356.86..166356.99 rows=13 width=17)
         -&gt;  Hash Join  (cost=6023.57..166338.87 rows=2398 width=17)
               Hash Cond: (lineorder.lo_orderdate = dwdate.d_datekey)
               -&gt;  Hash Join  (cost=5928.06..166207.39 rows=2398 width=17)
                     Hash Cond: (lineorder.lo_suppkey = supplier.s_suppkey)
                     -&gt;  Hash Join  (cost=5869.45..166084.74 rows=10682 width=21)
                           Hash Cond: (lineorder.lo_partkey = part.p_partkey)
                           -&gt;  Seq Scan on lineorder  (cost=0.00..137603.98 rows=6001198 width=16)
                           -&gt;  Hash  (cost=5865.00..5865.00 rows=356 width=13)
                                 -&gt;  Seq Scan on part  (cost=0.00..5865.00 rows=356 width=13)
                                       Filter: (((p_brand1)::text &gt;= 'MFGR#2221'::text) AND ((p_brand1)
::text &lt;= 'MFGR#2228'::text))
                     -&gt;  Hash  (cost=53.00..53.00 rows=449 width=4)
                           -&gt;  Seq Scan on supplier  (cost=0.00..53.00 rows=449 width=4)
                                 Filter: ((s_region)::text = 'ASIA'::text)
               -&gt;  Hash  (cost=63.56..63.56 rows=2556 width=8)
                     -&gt;  Seq Scan on dwdate  (cost=0.00..63.56 rows=2556 width=8)
(18 rows)

Time: 0.821 ms
I found a better index. I think I was reading the query plan results, which caused me to determine the wrong index order.. Now I'm reading it from bottom to top, and it gives me this index:

create index index1 on lineorder(lo_partkey,lo_suppkey,lo_orderdate);


However, is there anyway of making it even better??