代码之家 › 专栏 › 技术社区 › Kevin Sylvestre

高效聚合JSONB阵列

postgresql

Kevin Sylvestre · 技术社区 · 11 年前

我有一个存储JSONB字段的表( data )包含类似Facebook的数据。数据结构为:

-
id         | 9403
kind       | 'likes'
data       | [{ id: "1", name: "Pluto", category: "Planet"}, { id: "2", name: "Saturn", category: "Planet" }]
-
id         | 9403
kind       | 'likes'
data       | [{ id: "2", name: "Neptune", category: "Planet"}, { id: "3", name: "Mars", category: "Planet" }]

目标是编写按类别聚合每个类别前N(5)个赞的查询。我有以下子查询,我不知道如何优化(使用索引或通过重写)。目标是获得名称和类别的计数分组,以便对它们进行排名。我从更简单的问题开始,高效地选择N个最受欢迎的:

SELECT
likes.entry->>'name' AS name,
likes.entry->>'category' AS category, 
COUNT(*) AS count
FROM (SELECT json_array_elements(metadata.data::JSON) AS entry FROM metadata WHERE metadata.kind = 'likes') AS likes
GROUP BY name, category
ORDER BY count DESC
LIMIT 5

该查询的运行时间已超过5秒(粘贴解释/分析):

Limit  (cost=39971.07..39971.07 rows=5 width=32) (actual time=5468.952..5468.954 rows=5 loops=1)
  ->  Sort  (cost=39971.07..39971.17 rows=200 width=32) (actual time=5468.952..5468.954 rows=5 loops=1)
        Sort Key: (count(*))
        Sort Method: top-N heapsort  Memory: 25kB
        ->  HashAggregate  (cost=39969.61..39970.41 rows=200 width=32) (actual time=5241.143..5376.502 rows=392515 loops=1)
              Group Key: (likes.entry ->> 'name'::text), (likes.entry ->> 'category'::text)
              ->  Subquery Scan on likes  (cost=0.00..34491.46 rows=3652100 width=32) (actual time=0.104..4552.531 rows=880073 loops=1)
                    ->  Seq Scan on metadata  (cost=0.00..19883.06 rows=3652100 width=703) (actual time=0.097..2146.678 rows=880073 loops=1)
                          Filter: ((kind)::text = 'likes'::text)
                          Rows Removed by Filter: 90145

我是否可以在不使用物化视图的情况下对其进行重构以提高速度/添加一些索引?我尝试添加以下(无用)索引:

CREATE INDEX index_metadata_on_likes_raw ON metadata USING gin(data) WHERE (kind = 'likes');
CREATE INDEX index_metadata_on_likes_targeted ON metadata ((data ->> 'name'), (data ->> 'category')) WHERE (kind = 'likes');

1 回复 | 直到 11 年前

sibnick 11 年前

你可以试试:

select name, category, COUNT(*) AS count from(
   SELECT jsonb_array_elements(test.data::JSONB)->>'name' as name, jsonb_array_elements(test.data::JSONB)->>'category' as category FROM test WHERE test.kind = 'likes') a 
GROUP BY name, category
ORDER BY count DESC
LIMIT 5;

推荐文章

Johnny T · 基于当前值的SQL合并表[重复]

1 年前

Kallydi · jdbc无法连接到docker中的postgresql数据库

1 年前

dietzi96 · Pandas DataFrame.to_sql随机和静默地失败,没有错误消息

1 年前

Peter Schofield · 类型转换Postgresql

1 年前

BarÄ±Å UÅaklÄ± · 如何在ON冲突更新中使用CTE中的值

1 年前

Kevin Smeeks · Pyspark JDBC分区读取

1 年前

Micah Armantrout · 如何使用sql脚本将数据插入到代码优先的数据库中?

1 年前

Vanortton · 使用Docker开始应用程序开发还是在开发过程中配置Docker?[关闭]

1 年前

Andrus · 如何在sql中查找第二个匹配项

1 年前

MWood · Postgres jsonpath用于引用单例元素或元素数组

1 年前