# SOME DESCRIPTIVE TITLE.
# FIRST AUTHOR <EMAIL@ADDRESS>, 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 <EMAIL@ADDRESS>\n"
"Language-Team: LANGUAGE <kde-i18n-doc@kde.org>\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 <ulink url=\"http://www.postgresql.org/docs/current/"
"static/storage-toast.html\">the PostgreSQL Documentation for TOAST</ulink> "
"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 &amp;&amp; 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 &amp;&amp; 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: <ulink url="
"\"http://archives.postgresql.org/pgsql-performance/2005-02/msg00030.php"
"\">http://archives.postgresql.org/pgsql-performance/2005-02/msg00030.php</"
"ulink>"
msgstr ""

#. Tag: para
#: performance_tips.xml:41
#, no-c-format
msgid ""
"and newer thread on PostGIS <ulink url=\"https://lists.osgeo.org/pipermail/"
"postgis-devel/2017-June/026209.html\">https://lists.osgeo.org/pipermail/"
"postgis-devel/2017-June/026209.html</ulink>"
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 &amp;&amp; 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 &amp;&amp; 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)&gt;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 <ulink url="
"\"https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server\">Tuning "
"your PostgreSQL Server</ulink>."
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 <code>ALTER "
"SYSTEM..</code> 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 <xref linkend=\"PostGIS_GUC\"/>."
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 ""
"<ulink url=\"https://www.postgresql.org/docs/current/static/runtime-config-"
"resource.html#GUC-MAX-WORKER-PROCESSES\">max_worker_processes</ulink> 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 ""
"<ulink url=\"http://www.postgresql.org/docs/current/static/runtime-config-"
"resource.html#GUC-WORK-MEM\">work_mem</ulink> (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 ""
"<ulink url=\"http://www.postgresql.org/docs/current/static/runtime-config-"
"resource.html#GUC-MAINTENANCE-WORK-MEM\">maintenance_work_mem</ulink> (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 ""
"<ulink url=\"https://www.postgresql.org/docs/current/static/runtime-config-"
"resource.html#GUC-MAX-PARALLEL-WORKERS-PER-GATHER"
"\">max_parallel_workers_per_gather</ulink> 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 <code>ST_Intersects</code> "
"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 "
"<code>max_worker_processes</code> 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 "
"<varname>Gather</varname> node. Parallel workers are taken from the pool of "
"processes established by <varname>max_worker_processes</varname>. 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 ""