Consider the following case which is almost exact snapshot of part of our scheme:
Table "cities" Column | Type | Modifiers | Description --------------+------------------------+-----------+------------- ficity_id | integer | not null | ficountry_id | integer | | firegion_id | integer | | fsname | character varying(100) | | fsname_ru | character varying(200) | | Indexes: "pk_geocities" PRIMARY KEY, btree (ficity_id) "idx_cities_name" btree (lower(fsname::text) varchar_pattern_ops) "idx_cities_name_ru" btree (lower(fsname_ru::text) varchar_pattern_ops) "idx_geocities_country_id" btree (ficountry_id) "idx_geocities_region_id" btree (firegion_id) Foreign-key constraints: "fk_geocities_country_id" FOREIGN KEY (ficountry_id) REFERENCES countries(ficountry_id) ON UPDATE CASCADE ON DELETE CASCADE "fk_geocities_region_id" FOREIGN KEY (firegion_id) REFERENCES regions(firegion_id) ON UPDATE CASCADE ON DELETE CASCADE Table "cities_name_words" Column | Type | Modifiers | Description -----------+------------------------+-----------+------------- ficity_id | integer | not null | fsword | character varying(200) | not null | Indexes: "idx_cities_name_words_city_id" btree (ficity_id) "idx_cities_name_words_word" btree (fsword varchar_pattern_ops) Foreign-key constraints: "fk_cities_name_words_city_id" FOREIGN KEY (ficity_id) REFERENCES cities(ficity_id) ON UPDATE CASCADE ON DELETE CASCADE Table "cities_name_ru_words" Column | Type | Modifiers | Description -----------+------------------------+-----------+------------- ficity_id | integer | not null | fsword | character varying(200) | not null | Indexes: "idx_cities_name_ru_words_city_id" btree (ficity_id) "idx_cities_name_ru_words_word" btree (fsword varchar_pattern_ops) Foreign-key constraints: "fk_cities_name_ru_words_city_id" FOREIGN KEY (ficity_id) REFERENCES cities(ficity_id) ON UPDATE CASCADE ON DELETE CASCADE This is the part of geo location database. The purpose of cities_name_words and cities_name_ru_words is to facilitate indexing on separate words in city name - they contain words of fsname or fsname_ru respectively of the corresponding record in cities if it has more than word. Cities has about 190000 records, cities_name_words about 80000 and cities_name_ru_words about 5000. Now the query for city by name looks like this: select * from cities where ( ficity_id in ( select ficity_id from cities_name_words where fsword like 'novgorod%' union select ficity_id from cities_name_ru_words where fsword like 'novgorod%' ) or lower(fsname) like 'novgorod%' or lower(fsname_ru) like 'novgorod%' ) QUERY PLAN Seq Scan on cities (cost=16.63..5949.26 rows=95014 width=60) Filter: ((hashed subplan) OR (lower((fsname)::text) ~~ 'novgorod%'::text) OR (lower((fsname_ru)::text) ~~ 'novgorod%'::text)) SubPlan -> Unique (cost=16.61..16.62 rows=2 width=4) -> Sort (cost=16.61..16.62 rows=2 width=4) Sort Key: cities_name_words.ficity_id -> Append (cost=0.00..16.60 rows=2 width=4) -> Index Scan using idx_cities_name_words_word on cities_name_words (cost=0.00..8.31 rows=1 width=4) Index Cond: (((fsword)::text ~>=~ 'novgorod'::text) AND ((fsword)::text ~<~ 'novgoroe'::text)) Filter: ((fsword)::text ~~ 'novgorod%'::text) -> Index Scan using idx_cities_name_ru_words_word on cities_name_ru_words (cost=0.00..8.27 rows=1 width=4) Index Cond: (((fsword)::text ~>=~ 'novgorod'::text) AND ((fsword)::text ~<~ 'novgoroe'::text)) Filter: ((fsword)::text ~~ 'novgorod%'::text) Notice how it uses proper indexes in subplan and goes for sequence scan on the main table. If the where- conditions are applied separately it uses indexes as expected: select * from cities where ( lower(fsname) like 'novgorod%' or lower(fsname_ru) like 'novgorod%' ) QUERY PLAN Bitmap Heap Scan on cities (cost=8.57..12.59 rows=1 width=60) Recheck Cond: ((lower((fsname)::text) ~~ 'novgorod%'::text) OR (lower((fsname_ru)::text) ~~ 'novgorod%'::text)) Filter: ((lower((fsname)::text) ~~ 'novgorod%'::text) OR (lower((fsname_ru)::text) ~~ 'novgorod%'::text)) -> BitmapOr (cost=8.57..8.57 rows=1 width=0) -> Bitmap Index Scan on idx_cities_name (cost=0.00..4.29 rows=1 width=0) Index Cond: ((lower((fsname)::text) ~>=~ 'novgorod'::text) AND (lower((fsname)::text) ~<~ 'novgoroe'::text)) -> Bitmap Index Scan on idx_cities_name_ru (cost=0.00..4.28 rows=1 width=0) Index Cond: ((lower((fsname_ru)::text) ~>=~ 'novgorod'::text) AND (lower((fsname_ru)::text) ~<~ 'novgoroe'::text)) select * from cities where ( ficity_id in ( select ficity_id from cities_name_words where fsword like 'novgorod%' union select ficity_id from cities_name_ru_words where fsword like 'novgorod%' ) ) QUERY PLAN Nested Loop (cost=16.61..33.24 rows=2 width=60) -> Unique (cost=16.61..16.62 rows=2 width=4) -> Sort (cost=16.61..16.62 rows=2 width=4) Sort Key: cities_name_words.ficity_id -> Append (cost=0.00..16.60 rows=2 width=4) -> Index Scan using idx_cities_name_words_word on cities_name_words (cost=0.00..8.31 rows=1 width=4) Index Cond: (((fsword)::text ~>=~ 'novgorod'::text) AND ((fsword)::text ~<~ 'novgoroe'::text)) Filter: ((fsword)::text ~~ 'novgorod%'::text) -> Index Scan using idx_cities_name_ru_words_word on cities_name_ru_words (cost=0.00..8.27 rows=1 width=4) Index Cond: (((fsword)::text ~>=~ 'novgorod'::text) AND ((fsword)::text ~<~ 'novgoroe'::text)) Filter: ((fsword)::text ~~ 'novgorod%'::text) -> Index Scan using pk_geocities on cities (cost=0.00..8.28 rows=1 width=60) Index Cond: (cities.ficity_id = cities_name_words.ficity_id) So, why does it estimate the row count as 95000 and chooses the seq scan path in the first query (even with enable_seqscan = off)? What can be done to make it use the index? Sincerely, Viatcheslav