postgresql - 从连接列优化 ORDER BY

Question

我有一个显示列表，其中列出了一个城市中的每个用户以及他们在表格中的最后一条消息，并按最新消息排在第一位:

Users
------
Caleb - Hey what's up?
------
Bill - Is there anything up tonight?
------
Jon - What's up man?
------

任何帮助优化下面的查询和/或帮助找出添加索引的位置都会非常有帮助。

我可能可以对 last_message_created_at 进行非规范化并存储在 users 上，但我想避免这种回填。

用户表:

                    Table "public.users"
      Column       |            Type             | Modifiers
-------------------+-----------------------------+-----------
 id                | integer                     | not null
 role              | user_role                   | not null
 last_message_id   | integer                     |
 city_id           | integer                     |

Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "ix_users_city_id" btree (city_id)
    "ix_users_last_message_id" btree (last_message_id)
Foreign-key constraints:
    "messages_last_message_id_fkey" FOREIGN KEY (last_message_id) REFERENCES messages(id)
    "users_city_id_fkey" FOREIGN KEY (city_id) REFERENCES cities(id)
Referenced by:
    TABLE "messages" CONSTRAINT "messages_from_user_id_fkey" FOREIGN KEY (from_user_id) REFERENCES users(id)
    TABLE "messages" CONSTRAINT "messages_to_user_id_fkey" FOREIGN KEY (to_user_id) REFERENCES users(id)

消息表:

                                        Table "public.messages"
      Column      |            Type             |                       Modifiers
------------------+-----------------------------+-------------------------------------------------------
 id               | integer                     | not null default nextval('messages_id_seq'::regclass)
 content          | character varying           |
 from_user_id     | integer                     |
 to_user_id       | integer                     |
 created_at       | timestamp without time zone |

Indexes:
    "messages_pkey" PRIMARY KEY, btree (id)
    "ix_messages_from_user_id" btree (from_user_id)
    "ix_messages_to_user_id" btree (to_user_id)
Foreign-key constraints:
    "messages_from_user_id_fkey" FOREIGN KEY (from_user_id) REFERENCES users(id)
    "messages_to_user_id_fkey" FOREIGN KEY (to_user_id) REFERENCES users(id)
Referenced by:
    TABLE "users" CONSTRAINT "messages_last_message_id_fkey" FOREIGN KEY (last_message_id) REFERENCES messages(id)

查询和计划:

EXPLAIN ANALYZE
SELECT users.city_id AS users_city_id, users.id AS users_id, users.last_message_id AS users_last_message_id
FROM users
JOIN messages ON messages.id = users.last_message_id
WHERE users.city_id = 1 ORDER BY users.last_message_id DESC;

QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=951606.67..951811.07 rows=81760 width=12) (actual time=1934.501..1998.216 rows=79454 loops=1)
   Sort Key: users.last_message_id
   Sort Method: quicksort  Memory: 6797kB
   ->  Nested Loop  (cost=1575.71..944935.41 rows=81760 width=12) (actual time=26.784..1817.478 rows=79454 loops=1)
         ->  Bitmap Heap Scan on users  (cost=1575.71..33209.21 rows=84040 width=12) (actual time=26.656..393.749 rows=85348 loops=1)
               Recheck Cond: (city_id = 1)
               ->  Bitmap Index Scan on ix_users_city_id  (cost=0.00..1554.70 rows=84040 width=0) (actual time=20.679..20.679 rows=85348 loops=1)
                     Index Cond: (city_id = 1)
         ->  Index Only Scan using messages_pkey on messages  (cost=0.00..10.84 rows=1 width=4) (actual time=0.012..0.013 rows=1 loops=85348)
               Index Cond: (id = users.last_message_id)
               Heap Fetches: 79454
 Total runtime: 2058.134 ms
(12 rows)

Answer 1

除了排除没有消息的用户之外，您绝不会在查询中使用 messages 表以达到任何其他目的，因此这可能会更快:

select
    u.city_id as users_city_id,
    u.id as users_id,
    u.last_message_id as users_last_message_id
from users u
where
    u.city_id = 1
    and exists (
        select 1
        from messages
        where id = u.last_message_id
    )
order by u.last_message_id desc