CODEWITHSUNDEEP
postgresql
Fabiano
Fabiano

Reputation: 133

Unexpected query plan

I have a table "ps_wms.estoque" with 7 UUID columns. Each column have a sigle index on it. The table also have a unique index on all columns.

create unique index idx on ps_wms.estoque (endereco_id, material_id, ..., origem_id);

The query below has a where clause using all columns from the unique index, but according to query plan it is using a index on a single column (column programacao_id), which is a column that keeps the same value for all records. It seems to me this query should use the unique index, shouldn't it?

select
    * 
from
    ps_wms.estoque a 
where
    a.endereco_id = '8d4d99b1-98f3-4768-bbdf-3768dc2be341'
    and a.material_id = '8d4d99b1-98f3-4768-bbdf-3768dc2be341' 
    and a.reserva_id = '8d4d99b1-98f3-4768-bbdf-3768dc2be341'
    and a.programacao_id = '8d4d99b1-98f3-4768-bbdf-3768dc2be341'
    and a.uma_id = '8d4d99b1-98f3-4768-bbdf-3768dc2be341'
    and a.tipo = '8d4d99b1-98f3-4768-bbdf-3768dc2be341'
    and a.origem_id = '8d4d99b1-98f3-4768-bbdf-3768dc2be341'

Index Scan using estoque_fk5 on ps_wms.estoque a
  Output: id, armazem_id, endereco_id, material_id, reserva_id, programacao_id, quantidade, version, uma_id, tipo, origem_id
  Index Cond: ((a.programacao_id)::uuid = '8d4d99b1-98f3-4768-bbdf-3768dc2be341'::uuid)
  Filter: (((a.endereco_id)::uuid = '8d4d99b1-98f3-4768-bbdf-3768dc2be341'::uuid) AND ((a.material_id)::uuid = '8d4d99b1-98f3-4768-bbdf-3768dc2be341'::uuid) AND ((a.reserva_id)::uuid = '8d4d99b1-98f3-4768-bbdf-3768dc2be341'::uuid) AND ((a.uma_id)::uuid = ' (...)

DDL

CREATE INDEX estoque_fk1 ON ps_wms.estoque (endereco_id);
CREATE INDEX estoque_fk2 ON ps_wms.estoque (material_id);
CREATE INDEX estoque_fk3 ON ps_wms.estoque (reserva_id);
CREATE INDEX estoque_fk4 ON ps_wms.estoque (armazem_id);
CREATE INDEX estoque_fk5 ON ps_wms.estoque (programacao_id);
CREATE INDEX estoque_fk6 ON ps_wms.estoque (uma_id);
CREATE INDEX estoque_fk7 ON ps_wms.estoque (origem_id);
CREATE UNIQUE INDEX iii ON ps_wms.estoque (endereco_id,material_id,reserva_id,programacao_id,uma_id,tipo,origem_id);

Upvotes: 0

Views: 27

Answers (1)

Fabiano
Fabiano

Reputation: 133

I figured out what is happening.

The values I used in the where clause was random values.

As the column 'programacao_id' has just one value for all rows (n_distinct = 1) and the value I passed in the where clause is not this value, the engine knows in advance based on this column that this query will return an empty resultset.

If I use real values in the where clause, the plan choose the unique index as expected.

Upvotes: 1

Related Questions