1
  • PostgreSQL

I executed below query for 4 million of geometry polygon in PostgreSQL. It took unlimited time which I need to stop the execution after 2 hour.

 SELECT 
      c.id, 
     c.classification,
    ST_Difference(b.geom, c.geom)
    FROM 
      public."city" c,
    public.b
    where 
    c.classificationin ('Class I','Sub Metro City','Class II','Metro City','Class III','Mega City');
  • QGIS

The same layer I used in QGIS using Difference in Vector >> Geoprocessing Tools and it took hardly 3 minutes to clip the region.

Please help me out

how can i optimize the query in PostgreSQL ?

dmci
  • 4,882
  • 2
  • 19
  • 31
Bhavesh Bhatia
  • 11
  • 1
  • 1
    Check first that both tables have spatial index. Then you could have a try by adding AND b.geom && c.geom to your SQL. – user30184 Aug 29 '16 at 05:55
  • 1
    I would even extend the where clause with st_intersects(b.geom, c.geom), too, not to get empty geometry in the result, && works on the minimal boundary rectangle (MBR). Do you have an index on c.classification? – Zoltan Aug 29 '16 at 06:45
  • @Zoltan i also tried with the St_intersects, its takes the same time – Bhavesh Bhatia Aug 29 '16 at 07:09
  • @user30184 yes it does have spatial index on both the table – Bhavesh Bhatia Aug 29 '16 at 07:10
  • What does EXPLAIN QUERY show the query planner is doing? Also, make sure the code you are really running is what is above - fix the typos. – BradHards Aug 29 '16 at 07:17
  • I believe that the problem with the original query is that it is computing difference for every possible a-b pair and the product of (4 million * x) is huge. There must be a way to put more sense to the query and I believed that &&, or Intersects would be the way. Unfortunately it does not seem to be. How many features you have in a and in b? I do not quite understand what kind of data you have, could you add an image showing a few features from a and b? – user30184 Aug 29 '16 at 09:22

1 Answers1

1

Try putting the where clause in a sub query, so the selection is done first then the difference.

A similar problem can be found here

PostGIS doesn't use spatial index with ST_Intersects

Community
  • 1
Liam G
  • 2,156
  • 11
  • 11