i am trying to optimize posgis query times. i currently have two tables in the db:
cj_geometry:
id:integer; metadataId:text; location:geometry; groundgeometry:geometry; bbox: geometry; cj_feature:jsonb
and metadata:
id:integer; metadataId:text; translate:real[]; scale:real[]; epsg:text;filename:text
What i am trying to do is to query mvt-tiles from the db on the fly. i use a function similar (this example was for a different table layout) like this to achieve this:
the problem i am having is that the queries take long. too long. around 3s for a query is just not accepable.
what can i do to make these queries as fast as possible? indexing? clustering? what do i need to do?
Thanks a lot for ideas :)