Poor Query performance in postgresql 9.3 I have a simple query which is taking about 2 minutes to complete.

    select distinct
    a.statename,
    a.districtname,
    count(b.rel_globalid) as samplescollected 
    from fulladmin a 
    join gridpoint_attach_view b on a.globalid=b.rel_globalid
    where a.statename='Karnataka' group by statename, districtname order by districtname


Here fulladmin is a MATERIALIZED VIEW, which is having about 6.5 million rows.

    CREATE MATERIALIZED VIEW fulladmin AS 
    SELECT DISTINCT a.districtname,
    a.talukname,
    a.hobliname,
    a.villagename,
    a.statename,
    b.globalid
    FROM village a
    JOIN gridpoint_1 b ON st_intersects(a.shape, b.shape::st_geometry)
    WHERE a.statename::text = 'Karnataka'::text
    GROUP BY a.statename,
    a.districtname,
    a.talukname,
    a.hobliname,
    a.villagename,
    b.globalid
    WITH DATA;

   ALTER TABLE fulladmin
   OWNER TO sde;


and gridpoint_attach_view is normal view

     CREATE OR REPLACE VIEW sde.gridpoint_attach_view AS 
     SELECT gridpoint_1__attach.rel_globalid
     FROM gridpoint_1__attach;

     ALTER TABLE sde.gridpoint_attach_view
     OWNER TO sde;


I have created index on materialized view as 

CREATE INDEX sample1 ON fulladmin (statename,districtname,globalid) 


Still the query is taking too long. Any suggestion?

EDIT: When I run 

explain (analyse,verbose) select * from fulladmin


I am getting output as

seq scan on fulladmin (cost=0.00..141541.52 rows=5765552 width=84) (actual time=0.042..1160.429 rows=5765552 loops=1) 
  Output: districtname,talukname,hobliname,villagename,statename,globalid Total
runtime:1356.028 ms
This is an analytics query, analytics queries are never simple in terms of how much data they need to examine.

The where a.statename='Karnataka' is redundant, because it was already in CREATE MATERIALIZED VIEW and repeating it in your query has no effect, since it will match all rows. PostgreSQL sees this from cardinality and knows it needs to scan the full table.

Also, no index will help you here since you are touching a large portion of table. Indexes only work when touching small amounts of data.

If you run this query often, you need to cache it. If a value needs to be exact even as the original records are updated, you need a trigger.