From 29a314a092972e82cb0014499f60959d3f2909a4 Mon Sep 17 00:00:00 2001 From: Channgping Chen Date: Mon, 19 Apr 2021 00:01:01 +0000 Subject: [PATCH] fix index on location_property_tiger (parent_place_id) Looks like 2af82975cd968ec09683ae5b16a9aa157a7f2176 accidentally renamed an index. Because of the added "if not exists" clause, the index doesn't get created. This significantly slows down reverse queries because they now require full scans on location_property_tiger. Without this fix, reverse queries can take 8s on a full planet install on an r5.8xlarge instance in EC2. --- lib-sql/tiger_import_finish.sql | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/lib-sql/tiger_import_finish.sql b/lib-sql/tiger_import_finish.sql index 39ab1ae3..85bb5e3e 100644 --- a/lib-sql/tiger_import_finish.sql +++ b/lib-sql/tiger_import_finish.sql @@ -1,5 +1,5 @@ --index only on parent_place_id -CREATE INDEX {{sql.if_index_not_exists}} idx_location_property_tiger_place_id_imp +CREATE INDEX {{sql.if_index_not_exists}} idx_location_property_tiger_parent_place_id_imp ON location_property_tiger_import (parent_place_id) {{db.tablespace.aux_index}}; CREATE UNIQUE INDEX {{sql.if_index_not_exists}} idx_location_property_tiger_place_id_imp ON location_property_tiger_import (place_id) {{db.tablespace.aux_index}}; -- 2.45.1