[osmosis] 06/14: Add patch from upstream master branch: 0001-Manually-set-some-statistics-for-way_nodes-and-relat.patch

[Bas Couwenberg]

This is an automated email from the git hooks/post-receive script.

sebastic-guest pushed a commit to branch master
in repository osmosis.

commit 9f41ceeeeb89b0f38253151d6984a46cea1fb725
Author: Bas Couwenberg <sebastic at xs4all.nl>
Date:   Thu Apr 10 20:20:05 2014 +0200

    Add patch from upstream master branch: 0001-Manually-set-some-statistics-for-way_nodes-and-relat.patch
---
 ...t-some-statistics-for-way_nodes-and-relat.patch | 48 ++++++++++++++++++++++
 1 file changed, 48 insertions(+)

diff --git a/debian/patches/0001-Manually-set-some-statistics-for-way_nodes-and-relat.patch b/debian/patches/0001-Manually-set-some-statistics-for-way_nodes-and-relat.patch
new file mode 100644
index 0000000..ae609b4
--- /dev/null
+++ b/debian/patches/0001-Manually-set-some-statistics-for-way_nodes-and-relat.patch
@@ -0,0 +1,48 @@
+From 5542f816875e7a72382a3dc2c72ff53396603a8e Mon Sep 17 00:00:00 2001
+From: Paul Norman <penorman at mac.com>
+Date: Wed, 3 Jul 2013 00:04:53 -0700
+Subject: Manually set some statistics for way_nodes and relation_members
+Origin: https://github.com/openstreetmap/osmosis/commit/5542f816875e7a72382a3dc2c72ff53396603a8e
+
+Postgresql gets counts of distinct values horribly wrong for these tables.
+This sometimes leads to very bad query plans, particularly for antijoin patterns
+
+Because we have access to taginfo and know more about the data, we can do better.
+
+Values that are likely to scale with the size of the database are set to do so.
+
+Other values, like the maximum number of nodes in a way, are fixed based on current
+data.
+
+--- a/package/script/pgsnapshot_schema_0.6.sql
++++ b/package/script/pgsnapshot_schema_0.6.sql
+@@ -142,3 +142,29 @@ DECLARE
+ BEGIN
+ END;
+ $$ LANGUAGE plpgsql;
++
++-- Manually set statistics for the way_nodes and relation_members table
++-- Postgres gets horrible counts of distinct values by sampling random pages
++-- and can be off by an 1-2 orders of magnitude
++
++-- Size of the ways table / size of the way_nodes table
++ALTER TABLE way_nodes ALTER COLUMN way_id SET (n_distinct = -0.08);
++
++-- Size of the nodes table / size of the way_nodes table * 0.998
++-- 0.998 is a factor for nodes not in ways
++ALTER TABLE way_nodes ALTER COLUMN node_id SET (n_distinct = -0.83);
++
++-- API allows a maximum of 2000 nodes/way. Unlikely to impact query plans.
++ALTER TABLE way_nodes ALTER COLUMN sequence_id SET (n_distinct = 2000);
++
++-- Size of the relations table / size of the relation_members table
++ALTER TABLE relation_members ALTER COLUMN relation_id SET (n_distinct = -0.09);
++
++-- Based on June 2013 data
++ALTER TABLE relation_members ALTER COLUMN member_id SET (n_distinct = -0.62);
++
++-- Based on June 2013 data. Unlikely to impact query plans.
++ALTER TABLE relation_members ALTER COLUMN member_role SET (n_distinct = 6500);
++
++-- Based on June 2013 data. Unlikely to impact query plans.
++ALTER TABLE relation_members ALTER COLUMN sequence_id SET (n_distinct = 10000);

-- 
Alioth's /usr/local/bin/git-commit-notice on /srv/git.debian.org/git/pkg-grass/osmosis.git