postgresql - 如何减少查询时间

Question

我有一个 JSF PrimeFaces 数据表，它启用了延迟和分页选项，并查询到 PostgreSQL 数据库中的一个表。该表包含 7_000_000 行。

create table dkp_card_data(
id numeric(10) primary key,
name1 varchar(255),
name2 varchar(255),
name3 varchar(255),
value varchar(3999),
fk_id numeric(10) on update restrict on delete cascade);

create index idx on dkp_card_data (name1, name2, name3, value, fk_id);
create index inx_2 on dkp_card_data (fk_id);

问题是从数据库加载数据的时间过长。

我测量了 java 代码的时间，发现很多时间花在了 Jpa 存储库中的一个查询上。

这是方法:

@Query(value = "select distinct d.value from Data d where d.name1 = ?1 and d.name2 = ?2 and dcd.name = ?3")
Page<String> findValuesByName1AndName2AndName3WithPage(String name1, String name2, String name3, Pageable pageable);

Hibernate 生成查询并执行它们两次:

select distinct d.VALUE as col_0_0_
from DATA d
where d.NAME1=?and d.NAME2=?and d.NAME3=?
order by d.VALUE asc limit ?;

Limit  (cost=0.56..234.51 rows=5 width=9) (actual time=0.054..0.101 rows=5 loops=1)
  ->  Unique  (cost=0.56..164514.90 rows=3516 width=9) (actual time=0.053..0.100 rows=5 loops=1)
        ->  Index Only Scan using idx_constraint_dcdfk_tag_nm on data d  (cost=0.56..163259.98 rows=501966 width=9) (actual time=0.051..0.090 rows=21 loops=1)
              Index Cond: ((name1 = 'common'::text) AND (name2 = 'common'::text) AND (name2 = 'PPP'::text))
              Heap Fetches: 21
Planning time: 0.164 ms
Execution time: 0.131 ms

select count(distinct d.VALUE) as col_0_0_
from DATA d
where d.NAME1=?and d.NAME2=?and d.NAME3=?;

Aggregate  (cost=114425.94..114425.95 rows=1 width=8) (actual time=9457.205..9457.205 rows=1 loops=1)
  ->  Bitmap Heap Scan on data d  (cost=36196.62..113171.03 rows=501966 width=9) (actual time=256.187..1691.640 rows=502652 loops=1)
        Recheck Cond: (((name1)::text = 'common'::text) AND ((name2)::text = 'common'::text) AND ((name3)::text = 'PPP'::text))
        Rows Removed by Index Recheck: 2448858
        Heap Blocks: exact=41600 lossy=26550
          ->  Bitmap Index Scan on idx_constraint_dcdfk_tag_nm  (cost=0.00..36071.13 rows=501966 width=0) (actual time=243.261..243.261 rows=502668 loops=1)
                Index Cond: (((application_name)::text = 'common'::text) AND ((profile_name)::text = 'common'::text) AND ((tag_name)::text = 'PAN'::text))
Planning time: 0.174 ms
Execution time: 9457.931 ms

实际结果是8542毫秒。我找不到减少时间的方法。

Answer 1

由于 LIMIT，您的第一个查询速度很快 — 它使用索引检索 ORDER BY 顺序中的行，并在找到前 5 个结果后停止。

您的第二个查询不可能很快，因为它必须计算很多行。请注意，但是 Bitmap Heap Scan 期间的 lossy block :你有这些是因为你的 work_mem 太小而无法包含每个位图一位行。

如果你增加work_mem，例如通过

SET work_mem = '1GB';

查询将变得更快。尝试直到找到一个不太高但避免有损位图的值。