# SOME DESCRIPTIVE TITLE. # FIRST AUTHOR , YEAR. # #, fuzzy msgid "" msgstr "" "Project-Id-Version: PACKAGE VERSION\n" "Report-Msgid-Bugs-To: http://bugs.kde.org\n" "POT-Creation-Date: 2017-08-24 19:57+0000\n" "PO-Revision-Date: YEAR-MO-DA HO:MI+ZONE\n" "Last-Translator: FULL NAME \n" "Language-Team: LANGUAGE \n" "MIME-Version: 1.0\n" "Content-Type: text/plain; charset=UTF-8\n" "Content-Transfer-Encoding: 8bit\n" #. Tag: title #: performance_tips.xml:3 #, no-c-format msgid "Performance tips" msgstr "" #. Tag: title #: performance_tips.xml:6 #, no-c-format msgid "Small tables of large geometries" msgstr "" #. Tag: title #: performance_tips.xml:9 #, no-c-format msgid "Problem description" msgstr "" #. Tag: para #: performance_tips.xml:11 #, no-c-format msgid "" "Current PostgreSQL versions (including 9.6) suffer from a query optimizer " "weakness regarding TOAST tables. TOAST tables are a kind of \"extension room" "\" used to store large (in the sense of data size) values that do not fit " "into normal data pages (like long texts, images or complex geometries with " "lots of vertices), see the PostgreSQL Documentation for TOAST " "for more information)." msgstr "" #. Tag: para #: performance_tips.xml:19 #, no-c-format msgid "" "The problem appears if you happen to have a table with rather large " "geometries, but not too manyrows of them (like a table containing the " "boundaries of all European countries in high resolution). Then the table " "itself is small, but it uses lots of TOAST space. In our example case, the " "table itself had about 80 rows and used only 3 data pages, but the TOAST " "table used 8225 pages." msgstr "" #. Tag: para #: performance_tips.xml:26 #, no-c-format msgid "" "Now issue a query where you use the geometry operator && to search " "for a bounding box that matches only very few of those rows. Now the query " "optimizer sees that the table has only 3 pages and 80 rows. It estimates " "that a sequential scan on such a small table is much faster than using an " "index. And so it decides to ignore the GIST index. Usually, this estimation " "is correct. But in our case, the && operator has to fetch every " "geometry from disk to compare the bounding boxes, thus reading all TOAST " "pages, too." msgstr "" #. Tag: para #: performance_tips.xml:35 #, no-c-format msgid "" "To see whether your suffer from this issue, use the \"EXPLAIN ANALYZE\" " "postgresql command. For more information and the technical details, you can " "read the thread on the postgres performance mailing list: http://archives.postgresql.org/pgsql-performance/2005-02/msg00030.php" msgstr "" #. Tag: para #: performance_tips.xml:41 #, no-c-format msgid "" "and newer thread on PostGIS https://lists.osgeo.org/pipermail/" "postgis-devel/2017-June/026209.html" msgstr "" #. Tag: title #: performance_tips.xml:45 #, no-c-format msgid "Workarounds" msgstr "" #. Tag: para #: performance_tips.xml:47 #, no-c-format msgid "" "The PostgreSQL people are trying to solve this issue by making the query " "estimation TOAST-aware. For now, here are two workarounds:" msgstr "" #. Tag: para #: performance_tips.xml:50 #, no-c-format msgid "" "The first workaround is to force the query planner to use the index. Send " "\"SET enable_seqscan TO off;\" to the server before issuing the query. This " "basically forces the query planner to avoid sequential scans whenever " "possible. So it uses the GIST index as usual. But this flag has to be set on " "every connection, and it causes the query planner to make misestimations in " "other cases, so you should \"SET enable_seqscan TO on;\" after the query." msgstr "" #. Tag: para #: performance_tips.xml:58 #, no-c-format msgid "" "The second workaround is to make the sequential scan as fast as the query " "planner thinks. This can be achieved by creating an additional column that " "\"caches\" the bbox, and matching against this. In our example, the commands " "are like:" msgstr "" #. Tag: programlisting #: performance_tips.xml:63 #, no-c-format msgid "" "SELECT " "AddGeometryColumn('myschema','mytable','bbox','4326','GEOMETRY','2');\n" "UPDATE mytable SET bbox = ST_Envelope(ST_Force2D(the_geom));" msgstr "" #. Tag: para #: performance_tips.xml:65 #, no-c-format msgid "" "Now change your query to use the && operator against bbox instead of " "geom_column, like:" msgstr "" #. Tag: programlisting #: performance_tips.xml:68 #, no-c-format msgid "" "SELECT geom_column\n" "FROM mytable\n" "WHERE bbox && ST_SetSRID('BOX3D(0 0,1 1)'::box3d,4326);" msgstr "" #. Tag: para #: performance_tips.xml:70 #, no-c-format msgid "" "Of course, if you change or add rows to mytable, you have to keep the bbox " "\"in sync\". The most transparent way to do this would be triggers, but you " "also can modify your application to keep the bbox column current or run the " "UPDATE query above after every modification." msgstr "" #. Tag: title #: performance_tips.xml:79 #, no-c-format msgid "CLUSTERing on geometry indices" msgstr "" #. Tag: para #: performance_tips.xml:81 #, no-c-format msgid "" "For tables that are mostly read-only, and where a single index is used for " "the majority of queries, PostgreSQL offers the CLUSTER command. This command " "physically reorders all the data rows in the same order as the index " "criteria, yielding two performance advantages: First, for index range scans, " "the number of seeks on the data table is drastically reduced. Second, if " "your working set concentrates to some small intervals on the indices, you " "have a more efficient caching because the data rows are spread along fewer " "data pages. (Feel invited to read the CLUSTER command documentation from the " "PostgreSQL manual at this point.)" msgstr "" #. Tag: para #: performance_tips.xml:91 #, no-c-format msgid "" "However, currently PostgreSQL does not allow clustering on PostGIS GIST " "indices because GIST indices simply ignores NULL values, you get an error " "message like:" msgstr "" #. Tag: programlisting #: performance_tips.xml:95 #, no-c-format msgid "" "lwgeom=# CLUSTER my_geom_index ON my_table;\n" "ERROR: cannot cluster when index access method does not handle null values\n" "HINT: You may be able to work around this by marking column \"the_geom\" NOT " "NULL." msgstr "" #. Tag: para #: performance_tips.xml:97 #, no-c-format msgid "" "As the HINT message tells you, one can work around this deficiency by adding " "a \"not null\" constraint to the table:" msgstr "" #. Tag: programlisting #: performance_tips.xml:100 #, no-c-format msgid "" "lwgeom=# ALTER TABLE my_table ALTER COLUMN the_geom SET not null;\n" "ALTER TABLE" msgstr "" #. Tag: para #: performance_tips.xml:102 #, no-c-format msgid "" "Of course, this will not work if you in fact need NULL values in your " "geometry column. Additionally, you must use the above method to add the " "constraint, using a CHECK constraint like \"ALTER TABLE blubb ADD CHECK " "(geometry is not null);\" will not work." msgstr "" #. Tag: title #: performance_tips.xml:109 #, no-c-format msgid "Avoiding dimension conversion" msgstr "" #. Tag: para #: performance_tips.xml:111 #, no-c-format msgid "" "Sometimes, you happen to have 3D or 4D data in your table, but always access " "it using OpenGIS compliant ST_AsText() or ST_AsBinary() functions that only " "output 2D geometries. They do this by internally calling the ST_Force2D() " "function, which introduces a significant overhead for large geometries. To " "avoid this overhead, it may be feasible to pre-drop those additional " "dimensions once and forever:" msgstr "" #. Tag: programlisting #: performance_tips.xml:118 #, no-c-format msgid "" "UPDATE mytable SET the_geom = ST_Force2D(the_geom);\n" "VACUUM FULL ANALYZE mytable;" msgstr "" #. Tag: para #: performance_tips.xml:120 #, no-c-format msgid "" "Note that if you added your geometry column using AddGeometryColumn() " "there'll be a constraint on geometry dimension. To bypass it you will need " "to drop the constraint. Remember to update the entry in the geometry_columns " "table and recreate the constraint afterwards." msgstr "" #. Tag: para #: performance_tips.xml:126 #, no-c-format msgid "" "In case of large tables, it may be wise to divide this UPDATE into smaller " "portions by constraining the UPDATE to a part of the table via a WHERE " "clause and your primary key or another feasible criteria, and running a " "simple \"VACUUM;\" between your UPDATEs. This drastically reduces the need " "for temporary disk space. Additionally, if you have mixed dimension " "geometries, restricting the UPDATE by \"WHERE dimension(the_geom)>2\" " "skips re-writing of geometries that already are in 2D." msgstr "" #. Tag: title #: performance_tips.xml:138 #, no-c-format msgid "Tuning your configuration" msgstr "" #. Tag: para #: performance_tips.xml:140 #, no-c-format msgid "" "Tuning for PostGIS is much like tuning for any PostgreSQL workload. The only " "additional note to keep in mind is that geometries and rasters are heavy so " "memory related optimizations generally have more of an impact on PostGIS " "than other types of PostgreSQL queries." msgstr "" #. Tag: para #: performance_tips.xml:143 #, no-c-format msgid "" "For general details about optimizing PostgreSQL, refer to Tuning " "your PostgreSQL Server." msgstr "" #. Tag: para #: performance_tips.xml:145 #, no-c-format msgid "" "For PostgreSQL 9.4+ all these can be set at the server level without " "touching postgresql.conf or postgresql.auto.conf by using the ALTER " "SYSTEM.. command." msgstr "" #. Tag: programlisting #: performance_tips.xml:147 #, no-c-format msgid "" "ALTER SYSTEM SET work_mem = '256MB';\n" "-- this will force, non-startup configs to take effect for new connections\n" "SELECT pg_reload_conf();\n" "-- show current setting value\n" "-- use SHOW ALL to see all settings\n" "SHOW work_mem;" msgstr "" #. Tag: para #: performance_tips.xml:149 #, no-c-format msgid "" "In addition to these settings, PostGIS also has some custom settings which " "you can find listed in ." msgstr "" #. Tag: title #: performance_tips.xml:152 #, no-c-format msgid "Startup" msgstr "" #. Tag: para #: performance_tips.xml:154 #, no-c-format msgid "These settings are configured in postgresql.conf:" msgstr "" #. Tag: ulink #: performance_tips.xml:159 #, no-c-format msgid "constraint_exclusion" msgstr "" #. Tag: para #: performance_tips.xml:164 #, no-c-format msgid "Default: partition" msgstr "" #. Tag: para #: performance_tips.xml:169 #, no-c-format msgid "" "This is generally used for table partitioning. The default for this is set " "to \"partition\" which is ideal for PostgreSQL 8.4 and above since it will " "force the planner to only analyze tables for constraint consideration if " "they are in an inherited hierarchy and not pay the planner penalty otherwise." msgstr "" #. Tag: ulink #: performance_tips.xml:178 #, no-c-format msgid "shared_buffers" msgstr "" #. Tag: para #: performance_tips.xml:183 #, no-c-format msgid "Default: ~128MB in PostgreSQL 9.6" msgstr "" #. Tag: para #: performance_tips.xml:188 #, no-c-format msgid "" "Set to about 25% to 40% of available RAM. On windows you may not be able to " "set as high." msgstr "" #. Tag: para #: performance_tips.xml:195 #, no-c-format msgid "" "max_worker_processes This " "setting is only available for PostgreSQL 9.4+. For PostgreSQL 9.6+ this " "setting has additional importance in that it controls the max number of " "processes you can have for parallel queries." msgstr "" #. Tag: para #: performance_tips.xml:203 #, no-c-format msgid "Default: 8" msgstr "" #. Tag: para #: performance_tips.xml:208 #, no-c-format msgid "" "Sets the maximum number of background processes that the system can support. " "This parameter can only be set at server start." msgstr "" #. Tag: title #: performance_tips.xml:218 #, no-c-format msgid "Runtime" msgstr "" #. Tag: para #: performance_tips.xml:220 #, no-c-format msgid "" "work_mem (the memory used for sort " "operations and complex queries)" msgstr "" #. Tag: para #: performance_tips.xml:226 #, no-c-format msgid "Default: 1-4MB" msgstr "" #. Tag: para #: performance_tips.xml:231 #, no-c-format msgid "Adjust up for large dbs, complex queries, lots of RAM" msgstr "" #. Tag: para #: performance_tips.xml:236 #, no-c-format msgid "Adjust down for many concurrent users or low RAM." msgstr "" #. Tag: para #: performance_tips.xml:241 #, no-c-format msgid "If you have lots of RAM and few developers:" msgstr "" #. Tag: programlisting #: performance_tips.xml:243 #, no-c-format msgid "SET work_mem TO '256MB';" msgstr "" #. Tag: para #: performance_tips.xml:248 #, no-c-format msgid "" "maintenance_work_mem (used " "for VACUUM, CREATE INDEX, etc.)" msgstr "" #. Tag: para #: performance_tips.xml:254 #, no-c-format msgid "Default: 16-64MB" msgstr "" #. Tag: para #: performance_tips.xml:259 #, no-c-format msgid "Generally too low - ties up I/O, locks objects while swapping memory" msgstr "" #. Tag: para #: performance_tips.xml:264 #, no-c-format msgid "" "Recommend 32MB to 1GB on production servers w/lots of RAM, but depends on " "the # of concurrent users. If you have lots of RAM and few developers:" msgstr "" #. Tag: programlisting #: performance_tips.xml:267 #, no-c-format msgid "SET maintenance_work_mem TO '1GB';" msgstr "" #. Tag: para #: performance_tips.xml:272 #, no-c-format msgid "" "max_parallel_workers_per_gather This setting is only available " "for PostgreSQL 9.6+ and will only affect PostGIS 2.3+, since only PostGIS " "2.3+ supports parallel queries. If set to higher than 0, then some queries " "such as those involving relation functions like ST_Intersects " "can use multiple processes and can run more than twice as fast when doing " "so. If you have a lot of processors to spare, you should change the value of " "this to as many processors as you have. Also make sure to bump up " "max_worker_processes to at least as high as this number." msgstr "" #. Tag: para #: performance_tips.xml:282 #, no-c-format msgid "Default: 0" msgstr "" #. Tag: para #: performance_tips.xml:287 #, no-c-format msgid "" "Sets the maximum number of workers that can be started by a single " "Gather node. Parallel workers are taken from the pool of " "processes established by max_worker_processes. Note that " "the requested number of workers may not actually be available at run time. " "If this occurs, the plan will run with fewer workers than expected, which " "may be inefficient. Setting this value to 0, which is the default, disables " "parallel query execution." msgstr ""