sql - PostgreSQL:使用 JSON 时计数数据非常慢

Question

我在玩 PostgreSQL9.3 并决定尝试 JSON 类型。所以我写了一个查询，它计算某些 JSON 数据并按 user_id 对它们进行分组，但是这个过程需要很长时间才能完成。有人可以给我一个提示以提高性能吗？

但让我先向您展示测试数据和关系:

我下载了一些测试数据，其中每行包含一个 JSON 文档。 JSON 文档由一个名为“hashtags”的数组、一个名为“urls”的数组和一个名为“user_id”(非唯一)的字段组成。

测试数据:

{"hashtags": ["a", "b", "c"],"user_id": 210824047, "urls": ["http://www.google.com"]}
{"hashtags": ["b"], "user_id": 78149438, "urls": ["http://www.facebook.com"]}
{"hashtags": ["a", "c"], "user_id": 123791448, "urls": ["http://www.twitter.com","http://www.facebook.com"]}
...

然后我用一个自动递增的主键和一个包含如下 JSON 类型的列创建了一个简单的关系:

CREATE TABLE tweets(id BIGSERIAL PRIMARY KEY, tweet JSON);

我将数据加载到 postgresql 中，其中一个 JSON 文档现在等于一行:

id | tweet
---+---------------------------------------------------------------------------------
1  |{"hashtags": ["a", "b", "c"],"user_id": 210824047, "urls": ["http://www.google.com"]}
2  |{"hashtags": ["b"], "user_id": 78149438, "urls": ["http://www.facebook.com"]}
3  |{"hashtags": ["a", "c"], "user_id": 123791448, "urls": ["http://www.twitter.com","http://www.facebook.com"]}

现在我想知道某个 user_id 使用 hastag 的频率。要做到这一点，我有
使用使用 json_array_elements() 函数的子查询。该函数采用指定的 JSON 数组并将其转换为列。

查询如下所示:

    select foo.uid, foo.tag, count(foo.*) from 
        (select (tweet::json->>'user_id')::int as uid, 
        json_array_elements(tweet::json->'hashtags')::text as tag from tweets) 
    as foo group by foo.uid, foo.tag;

我得到了我想要的结果，但是查询需要很长时间才能完成。例如，我将 200.000 个 JSON 文档加载到 postgresql 中，略小于 25MB，大约需要 10 分钟才能得到结果。我也尝试了各种索引，但性能最多只提高了半分钟。

现在作为一个 postgresql 初学者，我问自己，如果它真的那么慢还是我做错了什么？如果是后者，我该怎么办？

您的帮助将不胜感激。

Answer 1

JSON 对 PostgreSQL 来说还是很新的东西，还没有得到很好的优化，但正在迅速改进。

当今年晚些时候发布 9.4 时，看起来这方面会有很大的改进(我得到大约 2 秒的时间来查询您的 196,608 条记录)。

实际上这种改进也在 9.3.3 中，应该会在一周内发布。