Open dmitrykinakh opened 2 years ago
This is really extremely slow. Is there an index on the geometry field (CREATE INDEX ON land_use_city USING GIST(geom);
) or could the Sieve
function on the geometry be slow? I would recommend using EXPLAIN
to find out which part of the query is slow.
Is there an index on the geometry field (CREATE INDEX ON land_use_city USING GIST(geom);)
There is an index for the geometry column. I've performed tests with it and without it for zoom levels 12 and 13 using the smaller machine. Generation time was the same. The blue color and first execution is zoom level 12 and no index. The red color and first execution is zoom level 13 and no index. The second execution for both zoom levels was with the index field added.
or could the Sieve function on the geometry be slow?
I don't think that this is the case since the same performance was before we started using that function.
I would recommend using EXPLAIN to find out which part of the query is slow
I'll try to get this information for you and post it here.
Here is a log from DB for all of the queries that are longer than 4 seconds postgresql.log.2021-11-25-11(more than 4 seconds).txt .
Geometry index usage looks fine. How's the timing without using the Sieve
function?
This is a log without using the Sieve
function
postgresql.log.2021-11-26-13.txt
Also, this is a generation timing using ec2 r5.4xlarge (16 vCPU 128 GB RAM) and with DB db.r6g.4xlarge (16 vCPU 128 GB RAM) without the Sieve
function.
Zoom | With Sieve | Without Sieve |
---|---|---|
Z10 | 0m 20.023s | 0m 22.549s |
Z11 | 0m 14.679s | 0m 8.360s |
Z12 | 0m 31.691s | 0m 15.165s |
Z13 | 1m 55.904s | 0m 20.980s |
Z14 | 6m 17.114s | 0m 37.817s |
Z15 | 19m 56.263s | 2m 13.246s |
Z16 | 73m 25.188s | 5m 19.229s |
Z17 | 284m 10.441s | 11m 45.406s |
Z18 | 1130m 6.336s | 34m 49.984s |
Looks like I know the answer to why generation is so slow. However, still, wondering what it's better - polygon or multipolygon?
Hi there,
in the project we are building at the moment the biggest problem is tiles generation speed.
So, these are input parameters:
T-Rex version 0.14.1
Config in toml.
Number of records in DB - 13231 Number of records that match query in selected bbox - 60
Generation logs for:
Timing
Generation was performed on ec2 r5.4xlarge (16 vCPU 128 GB RAM) and with DB db.r6g.4xlarge (16 vCPU 128 GB RAM)
With bigger zoom levels, like 17 or 18 this takes hours. We are wondering if the issue is in data that we store as multipolygons. Those multipolygons contain from 1 to 2-3K individual polygons.
From the performance perspective, would it be better to have these data represented in DB as polygons, so instead of 60 records, in our case, we will have several thousand? Looking forward to recommendations.
Note: this is a part of the query that takes the most of the time: