使用联接查询检索行 [英] Retrieving the rows using join query

问题描述

我有两个这样的表

A             B
----          -------       
col1  col2    col1   col2
----------    -----------

表包含30万行
B表包含40万行

A table contains 300k rows B table contains 400k rows

我需要对表A的col1进行计数它匹配表B的col1
我写了这样的查询：

I need to count the col1 for table A if it is matching col1 for table B I have written a query like this:

select count(distinct ab.col1) from A ab join B bc on(ab.col1=bc.col1)

很多时间

推荐答案

不提出更多详细信息就很难回答：您是否分析过表格？在每个表的col1上都有索引吗？您要计算多少行？

It's difficult to answer without you positing more details: did you analyze the tables? Do you have an index on col1 on each table? How many rows are you counting?

话虽如此，您的查询没有太多潜在的查询计划。您可能有两个通过散列连接在一起的seq扫描，这是您可以做的最好的事情。...如果您有大量的行，则将计算成千亿万行，这需要时间。

That being said, there aren'y so many potential query plans for your query. You likely have two seq scans that are hash joined together, which is about the best you can do... If you've a material numbers of rows, you'll be counting a gazillion rows, and this takes time.

也许您可以用不同的方式重写查询？如果每个B.col1都在A.col1中，则无需加入即可获得相同的结果：

Perhaps you could rewrite the query differently? If every B.col1 is in A.col1, you could get the same result without the join:

select count(distinct col1) from B

如果A的基数较低，则依靠exist（）可能会更快：

If A has low cardinality, it might be faster to rely on exists():

with vals as (
  select distinct A.col1 as val from A
)
select count(*) from vals
where exists(select 1 from B where B.col1 = vals.val)

或者，如果您知道A.col1中的每个可能值，并且它相当小，则可以取消嵌套数组而根本不查询A：

Or, if you know every possible value from A.col1 and it's reasonably small, you could unnest an array without querying A at all:

select count(*) from unnest(Array[val1, val2, ...]) as vals (val)
where exists(select 1 from B where B.col1 = vals.val)

反之亦然，如果每个B都具有参考值，则反之亦然。

Or vice-versa, in each of the above, if every B holds the reference values.

这篇关于使用联接查询检索行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！