主页 / dba / 问题 / 264329
Accepted
lurf jurv
Asked: 2020-04-05 12:02:05 +0800 CST

从 Postgres 中非常小的部分索引中选择伪随机行？

  • 772

我有一个巨大的表，在满足特定条件的 <0.1% 的行上有一个很小的部分索引（一列为空）。我想伪随机选择满足此条件的行。我可以使用已经存在的部分索引来加快速度吗？如果可以，如何？

postgresql

1 个回答

  1. Best Answer

    应该不是问题，只需将部分索引的 WHERE 条件放入查询的 WHERE 条件即可。

    create table foobar as select id, case when random()<0.001 then NULL else random() END as nullable, random() as z from generate_series(1,1000000) f(id);
create index on foobar (id ) where nullable is null;
select * from foobar 
   where nullable is null order by random() limit 1;

    这确实必须读取所有索引行，所以它只能是快的。它还必须对所有行进行“排序”，但由于排序知道 LIMIT，因此它不是完整排序，因此不是真正的 Nlog(N)。

    如果你愿意用一列随机数来增加你的表格（我已经在我的例子中做过，只是为了当时的填充物），那么你可以做得更好。

    create table foobar as select id, case when random()<0.001 then NULL else random() END as nullable, random() as z from generate_series(1,50000000) f(id);
create index on foobar (z) where nullable is null;

with rand as (select random()/1.0005 as rand) 
select foobar.* from foobar,rand where nullable is null
  and z between rand and rand+ 0.0005 
order  by random() limit  1;

    您必须了解有多少索引行来调整常数 0.0005。如果您将其设置得太小，则该范围可能不包含行，因此您不会得到任何结果（然后您可以重试），如果您将其设置得太大，您会使用不必要的时间。

    如果不更改表，那么我认为您不能比读取所有索引元组做得更好；不愿意在随机性的质量上妥协。

    • 1

相关问题