postgresql - Postgres SQL性能上限(Postgres SQL performance ceiling)

Question

I have a flat table with ~4M rows and a multiindex on 5 columns.

(我有一个带有?4M行和5列上的multiindex的平面表。)

Simple select statements which return ~150 rows x 5 columns take roughly 700ms.

(返回150行x 5列的简单select语句大约需要700毫秒。)

I use all 5 multiindex columns in the where filter (in the same order as they're defined in the index itself).

(我在where过滤器中使用所有5个multiindex列（按照它们在索引本身中定义的顺序）。)

Is this the ceiling of postgres?

(这是postgres的上限吗？)

The query time is acceptably low, but I'd want to make it even lower if possible.

(查询时间可以接受地很短，但是我想尽可能减少它。)

Vacuuming does not help (autovacuum takes care of it anyhow with 1000+ runs in the last 2 months).

(吸尘无济于事（无论如何，自动吸尘器在最近2个月内运行了1000多次以上都可以解决此问题）。)

Explain Plan returns:

(解释计划退货：)

Index Scan using agg_data_level_idx on agg_data_level  (cost=0.56..8.58 rows=1 width=25)

Explain (analyze, buffers, format text) returns:

(说明（分析，缓冲区，格式化文本）返回：)

explain (analyze, buffers, format text) select planning_group_name, phone, email, chat from agg_data_level
where planning_group_name = 'French'
and region_name = 'Europe'
and country_name = 'Russia'
and business_name = 'Main'
and schedule_day_lcl_skey = '20191128';

Returns

(退货)

Index Scan using agg_data_level_idx on agg_data_level  (cost=0.56..8.58 rows=1 width=25) (actual time=0.051..0.287 rows=144 loops=1)
  Index Cond: (((planning_group_name)::text = 'French'::text) AND (schedule_day_lcl_skey = 20191128) AND ((region_name)::text = 'Europe'::text) AND ((country_name)::text = 'Russia'::text) AND ((business_name)::text = 'Main'::text))
  Buffers: shared hit=16
Planning time: 0.083 ms
Execution time: 0.309 ms

  ask by Bishonen_PL translate from so

Answer 1

Yes, in this query time there is not clear query only

(是的，在此查询时间内没有仅清除查询)

What is also included:

(还包括以下内容：)

- DataGrip operations like fetching etc.
- JDBC Driver's operations
- Network time