From b0129bea1ef6bf261d76cf72c4a9a98cc73cc456 Mon Sep 17 00:00:00 2001
From: Regina Obe <lr@pcorp.us>
Date: Sun, 19 Jun 2011 00:31:53 +0000
Subject: [PATCH] add index check / generation for soundex, lower, geometry
 gist, and least_hn

git-svn-id: http://svn.osgeo.org/postgis/trunk@7427 b70326c6-7e19-0410-871a-916f4a2858ee
---
 .../geocode/other_helper_functions.sql        | 56 +++++++++++++++++--
 1 file changed, 52 insertions(+), 4 deletions(-)

diff --git a/extras/tiger_geocoder/tiger_2010/geocode/other_helper_functions.sql b/extras/tiger_geocoder/tiger_2010/geocode/other_helper_functions.sql
index 7d7d953d6..38b1aaf48 100644
--- a/extras/tiger_geocoder/tiger_2010/geocode/other_helper_functions.sql
+++ b/extras/tiger_geocoder/tiger_2010/geocode/other_helper_functions.sql
@@ -23,19 +23,67 @@ $$ SELECT greatest(to_number( CASE WHEN trim($1) ~ '^[0-9]+$' THEN $1 ELSE '0' E
 
   
 -- Generate script to create missing indexes in tiger tables. 
--- This will generate sql you can run to index commonly used join columns in geocoder --
+-- This will generate sql you can run to index commonly used join columns in geocoder for tiger and tiger_data schemas --
 CREATE OR REPLACE FUNCTION missing_indexes_generate_script()
 RETURNS text AS
 $$
-SELECT array_to_string(ARRAY(SELECT 'CREATE INDEX idx_' || c.table_schema || '_' || c.table_name || '_' || c.column_name || ' ON ' || c.table_schema || '.' || c.table_name || ' USING btree(' || c.column_name || ')' As index
-FROM (SELECT * FROM 
+SELECT array_to_string(ARRAY(
+-- basic btree regular indexes
+SELECT 'CREATE INDEX idx_' || c.table_schema || '_' || c.table_name || '_' || c.column_name || ' ON ' || c.table_schema || '.' || c.table_name || ' USING btree(' || c.column_name || ')' As index
+FROM (SELECT table_name, table_schema  FROM 
 	information_schema.tables WHERE table_type = 'BASE TABLE') As t  INNER JOIN
 	(SELECT * FROM information_schema.columns WHERE column_name IN('countyfp','tlid', 'tfidl', 'tfidr', 'tfid', 'zip') ) AS c  
 		ON (t.table_name = c.table_name AND t.table_schema = c.table_schema)
 		LEFT JOIN pg_catalog.pg_indexes i ON 
 			(i.tablename = c.table_name AND i.schemaname = c.table_schema 
 				AND  indexdef LIKE '%' || c.column_name || '%') 
+WHERE i.tablename IS NULL AND c.table_schema IN('tiger','tiger_data')
+-- Gist spatial indexes --
+UNION ALL
+SELECT 'CREATE INDEX idx_' || c.table_schema || '_' || c.table_name || '_' || c.column_name || '_gist ON ' || c.table_schema || '.' || c.table_name || ' USING gist(' || c.column_name || ')' As index
+FROM (SELECT table_name, table_schema FROM 
+	information_schema.tables WHERE table_type = 'BASE TABLE') As t  INNER JOIN
+	(SELECT * FROM information_schema.columns WHERE column_name IN('the_geom', 'geom') ) AS c  
+		ON (t.table_name = c.table_name AND t.table_schema = c.table_schema)
+		LEFT JOIN pg_catalog.pg_indexes i ON 
+			(i.tablename = c.table_name AND i.schemaname = c.table_schema 
+				AND  indexdef LIKE '%' || c.column_name || '%') 
+WHERE i.tablename IS NULL AND c.table_schema IN('tiger','tiger_data')
+-- Soundex indexes --
+UNION ALL
+SELECT 'CREATE INDEX idx_' || c.table_schema || '_' || c.table_name || '_snd_' || c.column_name || ' ON ' || c.table_schema || '.' || c.table_name || ' USING btree(soundex(' || c.column_name || '))' As index
+FROM (SELECT table_name, table_schema FROM 
+	information_schema.tables WHERE table_type = 'BASE TABLE') As t  INNER JOIN
+	(SELECT * FROM information_schema.columns WHERE column_name IN('name', 'place', 'city') ) AS c  
+		ON (t.table_name = c.table_name AND t.table_schema = c.table_schema)
+		LEFT JOIN pg_catalog.pg_indexes i ON 
+			(i.tablename = c.table_name AND i.schemaname = c.table_schema 
+				AND  indexdef LIKE '%soundex(%' || c.column_name || '%') 
+WHERE i.tablename IS NULL AND c.table_schema IN('tiger','tiger_data') 
+    AND (c.table_name LIKE '%county%' OR c.table_name LIKE '%featnames' OR c.table_name  LIKE '%place' or c.table_name LIKE '%zip%') 
+-- Lower indexes --
+UNION ALL
+SELECT 'CREATE INDEX idx_' || c.table_schema || '_' || c.table_name || '_lower_' || c.column_name || ' ON ' || c.table_schema || '.' || c.table_name || ' USING btree(lower(' || c.column_name || '))' As index
+FROM (SELECT table_name, table_schema FROM 
+	information_schema.tables WHERE table_type = 'BASE TABLE') As t  INNER JOIN
+	(SELECT * FROM information_schema.columns WHERE column_name IN('name', 'place', 'city') ) AS c  
+		ON (t.table_name = c.table_name AND t.table_schema = c.table_schema)
+		LEFT JOIN pg_catalog.pg_indexes i ON 
+			(i.tablename = c.table_name AND i.schemaname = c.table_schema 
+				AND  indexdef LIKE '%lower(%' || c.column_name || '%') 
+WHERE i.tablename IS NULL AND c.table_schema IN('tiger','tiger_data') 
+    AND (c.table_name LIKE '%county%' OR c.table_name LIKE '%featnames' OR c.table_name  LIKE '%place' or c.table_name LIKE '%zip%') 
+-- Least address index btree least_hn(fromhn, tohn)
+UNION ALL
+SELECT 'CREATE INDEX idx_' || c.table_schema || '_' || c.table_name || '_least_address' || ' ON ' || c.table_schema || '.' || c.table_name || ' USING btree(least_hn(fromhn, tohn))' As index
+FROM (SELECT table_name, table_schema FROM 
+	information_schema.tables WHERE table_type = 'BASE TABLE' AND table_name LIKE '%addr' AND table_schema IN('tiger','tiger_data')) As t  INNER JOIN
+	(SELECT * FROM information_schema.columns WHERE column_name IN('fromhn') ) AS c  
+		ON (t.table_name = c.table_name AND t.table_schema = c.table_schema)
+		LEFT JOIN pg_catalog.pg_indexes i ON 
+			(i.tablename = c.table_name AND i.schemaname = c.table_schema 
+				AND  indexdef LIKE '%least_hn(%' || c.column_name || '%') 
 WHERE i.tablename IS NULL
-ORDER BY c.table_schema, c.table_name), E';\r');
+ORDER BY 1), E';\r');
 $$
 LANGUAGE sql VOLATILE;
\ No newline at end of file
-- 
2.50.1