English 中文(简体)
邮电局书面处理
原标题:Writing performant query for PostgreSQL

认为我们没有达到两个表

表1:路线

Id factory_port_id port_of_loading port_of_destination
1 1 2 3
2 3 1 2
3 3 1 2

表2:港口

Id port_name
1 mundra
2 kolkata
3 chennai

现在要向用户展示路线,我们需要在问询后加入表格。

SELECT
    r.Id,
    f.port_name AS factory_port_name,
    pl.port_name AS port_of_loading_name,
    pd.port_name AS port_of_destination_name
FROM
    Routes AS r
JOIN
    ports AS f ON r.factory_port_id = f.Id
JOIN
    ports AS pl ON r.port_of_loading = pl.Id
JOIN
    ports AS pd ON r.port_of_destination = pd.Id;

指数已列在表2的所有栏目中:

CREATE INDEX idx_port_name ON ports (port_name);
CREATE INDEX idx_factory_port_id ON Routes (factory_port_id);
CREATE INDEX idx_factory_port_id_1 ON Routes (port_of_loading);
CREATE INDEX idx_factory_port_id_2 ON Routes (port_of_destination);

Question How to make the above query performant as I need to filter at least 10 million rows in the route table and 1 million rows in the port tables.

我认为,路线表不通,避免加入。 这一良好想法吗?

是否有任何其他解决办法? 谁能帮助我找到一个? 我在谷歌上找不到任何东西。

问题回答

你的询问中没有过滤器,你正在询问所有表格中的所有内容。 指数通常无助于这种询问。 如果这些表格是大的,那么问题就会很缓慢;这是事情的性质。

您能做的是,确保您的表格为<代码>。 ANALYZEd 和VACUUMed,即您拥有快速磁盘和微粒(最好能持有所有表格)。 确保<代码>工作_mem与你可以确定的不忘。





相关问题
摘录数据

我如何将Excel板的数据输入我的Django应用? I m将PosgreSQL数据库作为数据库。

Postgres dump of only parts of tables for a dev snapshot

On production our database is a few hundred gigabytes in size. For development and testing, we need to create snapshots of this database that are functionally equivalent, but which are only 10 or 20 ...

How to join attributes in sql select statement?

I want to join few attributes in select statement as one for example select id, (name + + surname + + age) as info from users this doesn t work, how to do it? I m using postgreSQL.

What text encoding to use?

I need to setup my PostgreSQL DB s text encoding to handle non-American English characters that you d find showing up in languages such as German, Spanish, and French. What character encoding should ...

SQL LIKE condition to check for integer?

I am using a set of SQL LIKE conditions to go through the alphabet and list all items beginning with the appropriate letter, e.g. to get all books where the title starts with the letter "A": SELECT * ...

热门标签