sql - 在 Postgresql 中优化此计数查询

Question

我需要在我的应用程序中实现一个基本的方面搜索侧边栏。不幸的是，我不能使用 Elasticsearch/Solr/alternatives 并且仅限于 Postgres。

我有大约 10 多列（'status'、'classification'、'filing_type'...）我需要在每次搜索后返回每个不同值的计数并相应地显示它们。我已经起草了这段 sql，但是，从长远来看，这不会让我走得太远，因为一旦我达到大量的行，它就会大大减慢。

select row_to_json(t) from (
    select 'status' as column, status as value, count(*) from api_articles_mv_temp group by status 
  union
    select 'classification' as column, classification as value, count(*) from api_articles_mv_temp group by classification 
  union 
    select 'filing_type' as column, filing_type as value, count(*) from api_articles_mv_temp group by filing_type
  union
    ...) t;

这产生

 {"column":"classification","value":"State","count":2001}
 {"column":"classification","value":"Territory","count":23}
 {"column":"filing_type","value":"Joint","count":169}
 {"column":"classification","value":"SRO","count":771}
 {"column":"filing_type","value":"Single","count":4238}
 {"column":"status","value":"Updated","count":506}
 {"column":"classification","value":"Federal","count":1612}
 {"column":"status","value":"New","count":3901}

从查询计划来看，HashAggregates 正在减慢它的速度。

Subquery Scan on t  (cost=2397.58..2397.76 rows=8 width=32) (actual time=212.822..213.022 rows=8 loops=1)
  ->  HashAggregate  (cost=2397.58..2397.66 rows=8 width=186) (actual time=212.780..212.856 rows=8 loops=1)
         Group Key: ('status'::text), api_articles_mv_temp.status, (count(*))
         ->  Append  (cost=799.11..2397.52 rows=8 width=186) (actual time=75.238..212.701 rows=8 loops=1)
               ->  HashAggregate  (cost=799.11..799.13 rows=2 width=44) (actual time=75.221..75.242 rows=2 loops=1)
                     Group Key: api_articles_mv_temp.status
...

有没有更简单、更优化的方法来获得这个结果？

标签： sqlpostgresql

Answer 1

读取 api_articles_mv_temp 一次可能会提高性能。我给了你例子，你可以试试吗？

1. 如果“列”和“值”的组合是固定的，则查询如下所示：

select row_to_json(t) from (
  select "column", "value", count(*) as "count"
  from column_temp left outer join api_articles_mv_temp on
    "value"=
    case "column"
      when 'status' then status
      when 'classification' then classification
      when 'filing_type' then filing_type
    end
  group by "column", "value"
) t;

column_temp 的记录如下：

column         |value
---------------+----------
status         |New
status         |Updated
classification |State
classification |Territory
classification |SRO
filing_type    |Single
filing_type    |Joint

DB小提琴

2. 如果只修复了“列”，则查询如下所示：

select row_to_json(t) from (
  select "column",
    case "column"
      when 'status' then status
      when 'classification' then classification
      when 'filing_type' then filing_type
    end as "value",
    sum("count") as "count"
  from column_temp a
    cross join (
      select
        status,
        classification,
        filing_type,
        count(*) as "count"
      from api_articles_mv_temp
      group by
        status,
        classification,
        filing_type) b
  group by "column", "value"
) t;

column_temp 的记录如下：

column         
---------------
status         
classification 
filing_type    

DB小提琴