json - 如何使用大型 json 文档加速这个长时间运行的 postgres 查询？

Question

我们将不同的市场列表产品(ebay、亚马逊)聚合到一个应用程序中。列表表具有某些共享列，例如:

sku, title, price, quantity, state, etc..., common to all listings...

我们没有创建单独的多态表来存储特定于市场的列，而是利用 postgres 9.3 json 列并在列表表中创建了一个 listing_data 列来保存所有原始列表 json 数据我们从同一个列表表中的市场 API 获取它。

显示一个帐户的所有 eBay 列表的网页将进行这样的分页查询，我们使用 select 来限制某些列并从嵌套的 json 值中深度提取一些值:

SELECT
    id,
    sku,
    title,
    price,
    quantity,
    state,
    listing_data->>'listing_type' as listing_type,
    listing_data->>'listing_duration' as listing_duration,
    json_extract_path(listing_data, 'listing_details', 'end_time') as end_time,
    listing_data->>'start_price' as start_price,
    listing_data->>'buy_it_now_price' as buy_it_now_price,
    listing_data->>'reserve_price' as reserve_price,
    json_extract_path(listing_data, 'variations', 'variations') as ebay_variations
FROM "listings"
WHERE
    "listings"."channel_id" = $1 AND
    ("listings"."state" NOT IN ('deleted', 'archived', 'importing')) AND
    "listings"."state" IN ('online')
ORDER BY created_at DESC
LIMIT 25 OFFSET 0

问题是我们看到这个查询有时需要超过 30 秒并且在 heroku 上超时。我们正在使用 7G 的 postgres 内存的 Heroku Ika postgres 计划。我们在实践中发现，客户往往会放置大量的 HTML(甚至包括嵌入式二进制视频和 flash 应用程序!)，仅 ebay 描述就可以达到 500K。

下面是一个解释分析输出的例子，类似于上面的 select 语句:

Limit  (cost=0.11..58.72 rows=25 width=205) (actual time=998.693..1005.286 rows=25 loops=1)
  ->  Index Scan Backward using listings_manager_new on listings  (cost=0.11..121084.58 rows=51651 width=205) (actual time=998.691..1005.273 rows=25 loops=1)
        Index Cond: ((channel_id = xyz) AND ((state)::text = 'online'::text) AND ((type)::text = 'ListingRegular'::text))
Total runtime: 1005.330 ms

我的理解是 postgres 正在使用索引，但成本仍然很高？我读过 9.3 将 json 存储为文本 blob，因此从大型 json 文档中提取 json 数据值的成本很高，即使我们忽略了 description 键，整个 json 文档也需要解析。我们不是基于 json 数据进行过滤，我希望 json 解析成本仅与分页限制的 25 个结果相关，但我不确定。

我读过其他一些 stackoverflow 和博客，它们表明行的大小或具有“宽”表会影响性能，因为 postgres 查询超过 8k 页，而较大的行需要更多的磁盘 IO 来遍历更多页。我不知道这是否仅适用于顺序扫描或也适用于使用索引。

可能将 json 列移出主列表表并使 1-1 有一个与仅包含 json 的单独表关联，但这将需要对查询进行联接。

在我做任何事情之前，我想我会伸出援手并获得一些其他意见或建议，以了解我如何分析我们的瓶颈是什么，以及什么可能是加速此查询的解决方案。

Answer 1

Postgresql 使用一种称为 TOAST 的技术存储大属性，否则这些属性太大而无法存储在页面中。这样的属性被存储在一个单独的文件中，从它们所属的行中引用。

您的 JSON 属性存储在单个列中，因此如果描述字段与您所说的一样大，那么很可能整个 JSON 数据将使用 TOAST 存储许多这样的行。

如果查询完全引用该列，则需要读入整个列值，这会导致大量 I/O。如果在 WHERE 子句中引用了该列，它将产生最大的影响，但对于您显示的示例查询而言，情况似乎并非如此。但即使它只出现在 SELECT 子句中，也意味着必须为所有匹配行读入 TOAST 数据。

我的建议是:

• 如果你真的不需要大量的描述字段，那么在存储到数据库之前将其过滤掉
• 如果确实需要，请考虑将 JSON 数据拆分为两个字段，一列中包含经常访问的字段，另一列中包含其他较大且不常用的字段。
• 将较大的不常用字段完全放在一个单独的表中可能是个好主意。
• 避免在 WHERE 子句中使用 JSON 字段 - 尝试使用其他列中的数据限制查询
• 以上内容可能会让您重新考虑不使用继承表结构的原始 decstructure，特别是如果 JSON 结构的组件在 WHERE 子句中很有用。