The following PostGIS query is running very slow (> 1 day for about 1 million records):

UPDATE test.table1
    SET area= ST_Area(PC_EnvelopeGeometry(pa))
    WHERE area is null or area <> ST_Area(PC_EnvelopeGeometry(pa))::real

pa contains the point cloud patch (600 points) I had created following indexes:

  • spatial index (using Gist on PC_EnvelopeGeometry(pa)), binary index on area.

The PC has Core I7 processor, 64GB Ram with 1TB SSD & 4TB HDD. I've set the following Postgres server configuration:

shared_buffers = 13280MB, temp_buffers = 4096MB, work_mem = 2047MB, max_stack_depth = 3MB, 
min_parallel_table_scan_size = 5MB,
min_parallel_index_scan_size = 128kB
effective_cache_size = 18GB,
force_parallel_mode = on

Please advise what can be done to reduce the query processing time.

Your Answer

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

Browse other questions tagged or ask your own question.