我在 PostgreSQL 数据库中有一个大表(约 100 列,数百万行)。我的查询没有联接,但有几个 where 子句。除具有 形式的子句外,所有 where 子句均由适当的索引涵盖WHERE table.col_a > table.col_b
。我的问题是,如果我建立一个索引table(col_a, col_b)
来优化大于操作,或者我是否需要做一些特别的事情来提高效率?
编辑:根据 @frank-heikens 评论添加匿名查询/解释/ddl。
我在审查时确实注意到col_f
没有索引,我怀疑添加它可以解决我的大部分问题。也就是说,我仍然想知道优化table.col_b < table.col_a
.
如果有帮助,col_b
则几乎总是 0.0 并且col_a
始终为正。我的猜测是,这会降低性能优势,因为此子句过滤的行相对较少。欢迎就多列索引或表达式索引的相对成本/收益提出任何建议。
询问:
explain(analyze, verbose, buffers, settings)
SELECT id, col_a, col_b, col_c, col_d, col_e
FROM public.table
WHERE col_f = 'VALUE' AND
col_g != 'EXCLUDED_VALUE' AND
col_b < col_a AND
NOT ((col_h 'EXCLUDED_VALUE_1', 'EXCLUDED_VALUE_2') OR
col_h IS NULL)) AND
col_e IS NOT NULL
ORDER BY col_e DESC
LIMIT 5;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=10.61..10.62 rows=1 width=73) (actual time=0.101..0.102 rows=1 loops=1)
Output: id, col_a, col_b, col_c, col_d, col_e
Buffers: shared hit=2
-> Sort (cost=10.61..10.62 rows=1 width=73) (actual time=0.100..0.100 rows=1 loops=1)
Output: id, col_a, col_b, col_c, col_d, col_e
Sort Key: table.col_e DESC
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=2
-> Seq Scan on public.table (cost=0.00..10.60 rows=1 width=73) (actual time=0.046..0.083 rows=1 loops=1)
Output: id, col_a, col_b, col_c, col_d, col_e
Filter: ((table.col_h IS NOT NULL) AND (table.col_e IS NOT NULL) AND ((table.col_g)::text <> 'EXCLUDED_VALUE'::text) AND (table.col_b < table.col_a) AND ((table.col_h)::text <> ALL ('{EXCLUDED_VALUE_1,EXCLUDED_VALUE_2}'::text[])) AND ((table.col_f)::text = 'VALUE'::text))
Rows Removed by Filter: 10
Buffers: shared hit=2
Planning Time: 0.467 ms
Execution Time: 0.149 ms
(15 rows)
数据定义语言:
-- Dumped from database version 14.10 (Homebrew)
CREATE TABLE public.table (
id uuid DEFAULT gen_random_uuid() NOT NULL,
col_a double precision,
col_b double precision,
col_c boolean,
col_d character varying,
col_e timestamp(6) without time zone,
col_f character varying,
col_g character varying,
col_h character varying,
-- columns not referenced in the query have been elided
);
ALTER TABLE ONLY public.table
ADD CONSTRAINT table_pkey PRIMARY KEY (id);
CREATE INDEX index_table_on_col_e ON public.table USING btree (col_e);
CREATE INDEX index_table_on_col_g ON public.table USING btree (col_g);
CREATE INDEX index_table_on_col_h ON public.table USING btree (col_h);
-- indexes for columns not referenced in the query have been elided