如何在Postgresql中索引此查询？
如何在Postgresql中索引此查询？
提问于 2019-05-30 04:37:19
我正在尝试索引我的查询，以优化执行时间。我尝试了一些b-tree、hash、GIN和GISP索引，但它们都没有被Postgres规划器使用过。即使当我定义enable_seqscan = OFF来强制使用我的索引时，执行时间仍然存在或变得更糟。我怎样才能有效地索引这个查询？
Obs:英语不是我的主要语言，所以对于任何文本问题，我深表歉意
我使用三个表，每个表有10k、100k和100k个条目。请参阅下面的代码。我的PostgreSQL版本是9.6。
我尝试了一些索引：
-CREATE INDEX trgm_curso_index ON curso USING gin (nome_curso gin_trgm_ops);
-CREATE INDEX trgm_natureza_index ON instituicao USING gin (natureza_administrativa gin_trgm_ops);
-CREATE INDEX fk_curso ON curso(sigla, campus);
-CREATE INDEX fk_campus ON campus(sigla);
-CREATE INDEX curso_index ON curso (nome_curso);我的表：
CREATE TABLE instituicao (
sigla varchar(10),
nome_instituicao varchar(55) NOT NULL,
natureza_administrativa varchar(7) NOT NULL CHECK (natureza_administrativa IN ('Pública', 'Privada')),
nota_IGC smallint NOT NULL CHECK (nota_IGC >= 0 AND nota_IGC <= 5),
PRIMARY KEY(sigla)
);
CREATE TABLE campus(
nome_campus varchar(55) NOT NULL,
estado char(2) NOT NULL CHECK (estado IN ('AC', 'AL', 'AP', 'AM', 'BA', 'CE', 'DF', 'ES', 'GO', 'MA', 'MT', 'MS', 'MG', 'PA', 'PB', 'PR', 'PE', 'PI', 'RJ', 'RN', 'RS', 'RO', 'RR', 'SC', 'SP', 'SE', 'TO')),
cidade varchar(55) NOT NULL,
sigla varchar(10),
PRIMARY KEY(sigla, nome_campus),
FOREIGN KEY(sigla) REFERENCES instituicao(sigla) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE curso(
nome_curso varchar(55) NOT NULL,
area varchar(30) NOT NULL CHECK (area IN ('Ciências Exatas e da Terra', 'Ciências Biológicas', 'Engenharias', 'Ciências da Saúde', 'Ciências Agrárias', 'Ciências Sociais Aplicadas', 'Ciências Humanas', 'Linguística, Letras e Artes', 'Outros')),
nota_enade smallint NOT NULL CHECK (nota_enade >= 0 AND nota_enade <= 5),
grau varchar(12) NOT NULL CHECK (grau IN ('Bacharelado', 'Licenciatura', 'Mestrado', 'Doutorado', 'Tecnólogo')),
turno varchar(10) NOT NULL CHECK (turno IN ('Matutino', 'Integral', 'Noturno', 'Vespertino')),
duracao smallint NOT NULL CHECK (duracao > 0 AND duracao <= 12),
ano_criacao smallint NOT NULL CHECK (ano_criacao <= DATE_PART('YEAR', CURRENT_DATE) AND ano_criacao > 1980),
campus varchar(55) NOT NULL,
sigla varchar(10) NOT NULL,
PRIMARY KEY(sigla, campus, nome_curso),
FOREIGN KEY(sigla, campus) REFERENCES campus(sigla, nome_campus) ON DELETE CASCADE ON UPDATE CASCADE
);我的问题是：
SELECT curso.sigla, estado, campus, cidade, nome_curso, grau, turno,
duracao, nota_enade
FROM instituicao, campus, curso
WHERE instituicao.sigla = campus.sigla AND campus.nome_campus =
curso.campus AND campus.sigla = curso.sigla AND natureza_administrativa =
'Pública' AND nome_curso LIKE 'C%'
ORDER BY curso.sigla, estado, cidade;我的笔记本的实际执行时间是3.6-5.7秒，但我需要达到最大的1-2秒。
回答 1
Stack Overflow用户
回答已采纳
发布于 2019-05-30 06:20:33
您应该考虑复合索引，每个索引在这里尽可能地涵盖连接和过滤器。如果以下索引有帮助，请尝试。
CREATE INDEX curso_n_c_s
ON curso
(nome_curso,
campus,
sigla);
CREATE INDEX campus_n_s
ON campus
(nome_campus,
sigla);
CREATE INDEX instituicao_na_s
ON instituicao
(natureza_administrativa,
sigla);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56368162
复制相关文章
相似问题
腾讯云开发者
