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