SQL - 按索引列检索行 - 性能

Question

我有这张表，其中包含 payment_id 的索引(未显示):

CREATE TABLE myschema.payments
(
 payment_id bigint NOT NULL,
 box_id bigint ,
 mov_id bigint ,
 code_co character varying(5) NOT NULL,
 client_id bigint NOT NULL,
 user_created character varying(15) NOT NULL,
 date_payment timestamp without time zone NOT NULL,
)
;

这张表有近3000万条记录

我有一个这样的测试表

insert into dummy_table (payment_id) values (294343, 5456565);

此查询的解释分析在大约 4 分钟内检索到结果:

select * from myschema.payments where payment_id in (select payment_id from dummy_table )

但是，如果我执行这样的操作:

select * from myschema.payments where 
payment_id in (294343, 5456565);

我以毫秒为单位得到结果。

那些 payment_id 值是可变的，我怎样才能在每次执行时使用可变数量的不同 payment_id 来提高性能？如果有帮助，我的“in”语句每次都会有大约 20 个 payment_id。

这是查询的解释分析 select * from myschema.payments where payment_id in (select payment_id from dummy_table )

"Nested Loop Semi Join  (cost=100.00..6877.47 rows=137 width=274) (actual      time=47229.725..215893.809 rows=2 loops=1)"
"  Join Filter: (payments.payment_id = dummy_table.payment_id)"
"  Rows Removed by Join Filter: 47939387"
"  ->  Foreign Scan on payments  (cost=100.00..118.22 rows=274 width=274)      (actual time=1.334..198599.055 rows=23969695 loops=1)"
"  ->  Materialize  (cost=0.00..6751.03 rows=2 width=8) (actual time=0.000..0.000 rows=2 loops=23969695)"
"        ->  Seq Scan on dummy_table  (cost=0.00..6751.02 rows=2 width=8) (actual time=0.009..6.236 rows=2 loops=1)"
"Planning time: 0.238 ms"
"Execution time: 215894.462 ms"

编辑:为加入版本添加了解释分析:

select p.*
from myschema.payments p join
 dummy_table t
 on p.payment_id = t.payment_id;

"Nested Loop  (cost=100.00..6877.47 rows=3 width=274) (actual time=50680.577..228816.409 rows=2 loops=1)"
"  Join Filter: (payments.payment_id = dummy_table.payment_id)"
"  Rows Removed by Join Filter: 47939388"
"  ->  Foreign Scan on payments p  (cost=100.00..118.22 rows=274 width=274) (actual time=1.261..211380.739 rows=23969695 loops=1)"
"  ->  Materialize  (cost=0.00..6751.03 rows=2 width=8) (actual time=0.000..0.000 rows=2 loops=23969695)"
"        ->  Seq Scan on dummy_table t  (cost=0.00..6751.02 rows=2 width=8) (actual time=0.022..9.566 rows=2 loops=1)"
"Planning time: 0.311 ms"
"Execution time: 228817.094 ms"

Answer 1

尝试使用join:

select p.*
from myschema.payments p join
     dummy_table t
     on p.payment_id = t.payment_id;

试试这个版本。 . .这有点更蛮力:

select p.*
from dummy_table t left join
     myschema.payments p
     on p.payment_id = t.payment_id;