devel=> EXPLAIN (ANALYZE, VERBOSE) SELECT geonames.geoname_id, geonames.asciiname, geonames.feature, geonames.country_code, geonames.admin1, geonames.admin2, CASE WHEN geoname_names.name IS NOT NULL THEN geoname_names.name ELSE geonames.name END AS name FROM "geonames" LEFT OUTER JOIN geoname_names ON geoname_names.geoname_id = geonames.geoname_id AND geoname_names.name_id IN (SELECT name_id FROM geoname_names WHERE geoname_names.geoname_id = geonames.geoname_id AND geoname_names.language = 'ru' ORDER BY geoname_names.short DESC, geoname_names.preffered DESC, geoname_names.colloquial, geoname_names.historic LIMIT 1 ) WHERE country_code = 'RU' and admin1 = '17' ; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------- Nested Loop Left Join (cost=0.42..24679.25 rows=86 width=62) (actual time=80.404..102.200 rows=191 loops=1) Output: geonames.geoname_id, geonames.asciiname, geonames.feature, geonames.country_code, geonames.admin1, geonames.admin2, CASE WHEN (geoname_names.name IS NOT NULL) THEN geoname_names.name ELSE geonames.name END -> Seq Scan on public.geonames (cost=0.00..23228.67 rows=86 width=44) (actual time=80.341..100.420 rows=191 loops=1) Output: geonames.geoname_id, geonames.name, geonames.asciiname, geonames.latitude, geonames.longitude, geonames.feature, geonames.country_code, geonames.admin1, geonames.admin2, geonames.admin3, geonames.admin4, geonames.population, geonames.elevation, geonames.dem, geonames.timezone, geonames.modified_on, geonames.created_at, geonames.updated_at Filter: (((geonames.country_code)::text = 'RU'::text) AND ((geonames.admin1)::text = '17'::text)) Rows Removed by Filter: 673005 -> Index Scan using index_geoname_names_on_geoname_id_language_and_preferences on public.geoname_names (cost=0.42..16.86 rows=1 width=26) (actual time=0.009..0.009 rows=1 loops=191) Output: geoname_names.name_id, geoname_names.geoname_id, geoname_names.language, geoname_names.name, geoname_names.preffered, geoname_names.short, geoname_names.colloquial, geoname_names.historic, geoname_names.created_at, geoname_names.updated_at Index Cond: (geoname_names.geoname_id = geonames.geoname_id) Filter: (SubPlan 1) Rows Removed by Filter: 1 SubPlan 1 -> Limit (cost=8.46..8.46 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=305) Output: geoname_names_1.name_id, geoname_names_1.short, geoname_names_1.preffered, geoname_names_1.colloquial, geoname_names_1.historic -> Sort (cost=8.46..8.46 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=305) Output: geoname_names_1.name_id, geoname_names_1.short, geoname_names_1.preffered, geoname_names_1.colloquial, geoname_names_1.historic Sort Key: geoname_names_1.short, geoname_names_1.preffered, geoname_names_1.colloquial, geoname_names_1.historic Sort Method: quicksort Memory: 25kB -> Index Scan using index_geoname_names_on_geoname_id_language_and_preferences on public.geoname_names geoname_names_1 (cost=0.42..8.45 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=305) Output: geoname_names_1.name_id, geoname_names_1.short, geoname_names_1.preffered, geoname_names_1.colloquial, geoname_names_1.historic Index Cond: ((geoname_names_1.geoname_id = geonames.geoname_id) AND ((geoname_names_1.language)::text = 'ru'::text)) Total runtime: 102.260 ms (22 rows) devel=> EXPLAIN (ANALYZE, VERBOSE) SELECT geonames.geoname_id, geonames.asciiname, geonames.feature, geonames.country_code, geonames.admin1, geonames.admin2, CASE WHEN geoname_names.name IS NOT NULL THEN geoname_names.name ELSE geonames.name END AS name FROM "geonames" LEFT OUTER JOIN geoname_names ON geoname_names.geoname_id = geonames.geoname_id AND geoname_names.name_id IN (SELECT name_id FROM geoname_names WHERE geoname_names.geoname_id = geonames.geoname_id AND geoname_names.language = 'ru' ORDER BY geoname_names.short DESC, geoname_names.preffered DESC, geoname_names.colloquial, geoname_names.historic LIMIT 1 ) WHERE country_code = 'RU' and admin1 = '17' ORDER BY CASE WHEN geoname_names.name IS NOT NULL THEN geoname_names.name cs_devel-> ELSE geonames.name END; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------- Sort (cost=24682.02..24682.23 rows=86 width=62) (actual time=98.947..98.955 rows=191 loops=1) Output: geonames.geoname_id, geonames.asciiname, geonames.feature, geonames.country_code, geonames.admin1, geonames.admin2, (CASE WHEN (geoname_names.name IS NOT NULL) THEN geoname_names.name ELSE geonames.name END) Sort Key: (CASE WHEN (geoname_names.name IS NOT NULL) THEN geoname_names.name ELSE geonames.name END) Sort Method: quicksort Memory: 45kB -> Nested Loop Left Join (cost=0.42..24679.25 rows=86 width=62) (actual time=76.686..98.607 rows=191 loops=1) Output: geonames.geoname_id, geonames.asciiname, geonames.feature, geonames.country_code, geonames.admin1, geonames.admin2, CASE WHEN (geoname_names.name IS NOT NULL ) THEN geoname_names.name ELSE geonames.name END -> Seq Scan on public.geonames (cost=0.00..23228.67 rows=86 width=44) (actual time=76.621..96.473 rows=191 loops=1) Output: geonames.geoname_id, geonames.name, geonames.asciiname, geonames.latitude, geonames.longitude, geonames.feature, geonames.country_code, geonames.admin1, geonames.admin2, geonames.admin3, geonames.admin4, geonames.population, geonames.elevation, geonames.dem, geonames.timezone, geonames.modified_on, geonames.created_at, geonames.updated_at Filter: (((geonames.country_code)::text = 'RU'::text) AND ((geonames.admin1)::text = '17'::text)) Rows Removed by Filter: 673005 -> Index Scan using index_geoname_names_on_geoname_id_language_and_preferences on public.geoname_names (cost=0.42..16.86 rows=1 width=26) (actual time=0.010..0.011 rows=1 loops=191) Output: geoname_names.name_id, geoname_names.geoname_id, geoname_names.language, geoname_names.name, geoname_names.preffered, geoname_names.short, geoname_names.colloquial, geoname_names.historic, geoname_names.created_at, geoname_names.updated_at Index Cond: (geoname_names.geoname_id = geonames.geoname_id) Filter: (SubPlan 1) Rows Removed by Filter: 1 SubPlan 1 -> Limit (cost=8.46..8.46 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=305) Output: geoname_names_1.name_id, geoname_names_1.short, geoname_names_1.preffered, geoname_names_1.colloquial, geoname_names_1.historic -> Sort (cost=8.46..8.46 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=305) Output: geoname_names_1.name_id, geoname_names_1.short, geoname_names_1.preffered, geoname_names_1.colloquial, geoname_names_1.historic Sort Key: geoname_names_1.short, geoname_names_1.preffered, geoname_names_1.colloquial, geoname_names_1.historic Sort Method: quicksort Memory: 25kB -> Index Scan using index_geoname_names_on_geoname_id_language_and_preferences on public.geoname_names geoname_names_1 (cost=0.42..8.45 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=305) Output: geoname_names_1.name_id, geoname_names_1.short, geoname_names_1.preffered, geoname_names_1.colloquial, geoname_names_1.historic Index Cond: ((geoname_names_1.geoname_id = geonames.geoname_id) AND ((geoname_names_1.language)::text = 'ru'::text)) Total runtime: 99.000 ms (26 rows)