How I can index this query in Postgresql?

I’m trying to index my query to optimize the execution time. I tried some b-tree, hash, GIN and GISP index but none of them have been used by the Postgres planner. And even when I define enable_seqscan = OFF, to force the use of my indexes, the time of execution persists or get worse. How I can efficiently index this query?

Obs: English is not my main language, so sorry for any textual problems

I’m using three tables, with 10k, 100k and 100k entries each. See code below. And my PostgreSQL version is 9.6.

Some indexes that I tried:

-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);

My tables:

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
);

My query:

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;

The actual execution time in my notebook is 3.6-5.7 seconds, but I need to reach a maximum of 1-2 seconds.

Answer

You should think about compound indexes each covering the joins and the filter here as far as possible. Try if the following indexes help.

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);

Advertisement

Answer