0

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
  • 1
    Please edit your question and add the execution plan using explain (analyze, verbose) – a_horse_with_no_name Mar 22 '16 at 21:42
  • The distinct is redundant (in select distinct). Remove it and compare efficiency. – ypercubeᵀᴹ Mar 26 '16 at 10:02
  • You query only takes 1.3 seconds according to the explain analyze. The 2 minutes you are seeing are most probably caused by your SQL client to display over 5 million rows. – a_horse_with_no_name Mar 26 '16 at 10:16
0

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.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.