如何合并以下SQL查询以提高性能？

I have two SQL queries:

我有两个SQL查询：

Below query returns records in thousands(13000) and growing.

以下查询返回数千（13000）的记录并且不断增长。
```
SELECT distinct city FROM users;
```

The result of this above query becomes a parameter for next SQL query which is:

上面的查询结果成为下一个SQL查询的参数，它是：

SELECT CAST(users.lat as VARCHAR) AS latitude, 
       CAST(users.lng as VARCHAR) AS longitude,
        users.city as city,
        users.state as state 
FROM users users 
WHERE users .city='';

I have merged these two queries by making the first query a sub-query using IN clause as in:

我通过使用IN子句将第一个查询作为子查询来合并这两个查询，如下所示：

SELECT CAST(users.lat as VARCHAR) AS latitude, 
       CAST(users.lng as VARCHAR) AS longitude,
       users.city as city, 
       users.state as state 
FROM users users 
WHERE users.city IN (SELECT distinct us.city FROM users us);

Need to know if this can be optimized further.

需要知道是否可以进一步优化。

DDL:

DDL：

CREATE TABLE users
(
  id uuid NOT NULL,
  language_id integer NOT NULL,
  lat numeric NOT NULL,
  lng numeric NOT NULL,
  state character varying,
  city character varying,
  CONSTRAINT users_pkey PRIMARY KEY (id)
);

1 个解决方案

#1

First comment. You do not need distinct when using in. So this where clause suffices:

第一条评论。使用时不需要使用。所以这个where子句就足够了：

WHERE users.city IN (SELECT us.city FROM users us);

Second, it is very misleading to use a different table name as an alias for another table. Instead:

其次，使用不同的表名作为另一个表的别名是非常误导的。代替：

SELECT CAST(fru.lat as VARCHAR) AS latitude, 
       CAST(fru.lng as VARCHAR) AS longitude,
       fru.city as city, 
       fru.state as state 
FROM farmrise.users fru 
WHERE fru.city IN (SELECT u.city FROM users u);

Most databases will generate a good query plan for this. Personally, I would be inclined to write this as;

大多数数据库都会为此生成一个好的查询计划。就个人而言，我倾向于写这个;

FROM farmrise.users fru 
WHERE EXISTS (SELECT 1 FROM users u WHERE u.city = fru.city);

This will definitely take advantage of an index on users(city).

这肯定会利用用户（城市）的索引。

Finally, in most databases you want a length for the VARCHAR in the CAST(). In fact, you should have a length in any database other than MySQL and derivative databases.

最后，在大多数数据库中，您需要CAST（）中VARCHAR的长度。实际上，除了MySQL和衍生数据库之外，您应该在任何数据库中都有一个长度。

#1