postgresql - 如何优化 PostgreSQL 中时间戳的连接

Question

PostgreSQL 版本 10
Windows 10
16GB内存
固态硬盘

我很惭愧地承认，尽管搜索了百年的PG支持文件，我还是无法弄清楚这个最基本的问题。但这里是...
我的 big_table 有 4500 万行，little_table 有 12,000 行。我需要进行左连接以包含所有 big_table 行以及 Little_table 行的 id，其中 big_table 的时间戳与 Little_table 中的两个时间戳重叠。
这对于 PG 来说似乎不应该是一个极端的操作，但它需要 2 1/2 小时!
关于我可以在这里做什么有什么想法吗？或者您是否认为我无意中遇到了考虑到表大小的软件/硬件组合的限制？

谢谢!

具有 12,000 行的小表

CREATE TABLE public.little_table
(
    id bigint,
    start_time timestamp without time zone,
    stop_time timestamp without time zone
);


CREATE INDEX idx_little_table
    ON public.little_table USING btree
    (start_time, stop_time DESC);

包含 4500 万行的大表

CREATE TABLE public.big_table
(
    id bigint,
    datetime timestamp without time zone
) ;

CREATE INDEX idx_big_table
    ON public.big_table USING btree
    (datetime);

查询

explain analyze
select
  bt.id as bt_id,
  lt.id as lt_id
from
    big_table bt
left join
  little_table lt
on
    (bt.datetime between lt.start_time and  lt.stop_time)

解释结果

Nested Loop Left Join  (cost=0.29..3260589190.64 rows=64945831346 width=16) (actual time=0.672..9163998.367 rows=1374445323 loops=1)
  ->  Seq Scan on big_table bt  (cost=0.00..694755.92 rows=45097792 width=16) (actual time=0.014..10085.746 rows=45098790 loops=1)
  ->  Index Scan using idx_little_table on little_table lt  (cost=0.29..57.89 rows=1440 width=24) (actual time=0.188..0.199 rows=30 loops=45098790)
        Index Cond: ((bt.datetime >= start_time) AND (bt.datetime <= stop_time))
Planning time: 0.165 ms
Execution time: 9199473.052 ms

注意:我的实际查询条件有点复杂，但这似乎是问题的根源。如果我能解决这部分，我想我就能解决其余的问题。

Answer 1

此查询执行速度无法更快。

由于连接条件中没有相等运算符 (=)，因此 PostgreSQL 剩下的唯一策略是嵌套循环连接。对小表进行 4500 万次重复索引扫描只需要一段时间。