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.