Select que não usam PK ou Index das tabelas pequenas

[Bregnoles]

Por gentileza,
Verificando pelo Explain query do analyze do Postgres, de um SELECT * FROM ..., vejo que em algumas tabelas pequenas ele não usa a PK como regra de index.
Alguém poderia me dizer se isso ocorre e qual é o motivo?
E até quantos registros tem a tabela para ele considerar e usar  pesquisa sequencial ou pela PK ou índice?

Obrigado!

[Ronivaldo Lopes]

Boa noite, execute os comandos abaixo para desabilitar a busca sequencial e forçar o uso de índices. O SELECT em tabelas com poucos registros será utilizado o índice, caso exista.

  set enable_seqscan = off;
  set enable_indexscan = true;

 

Editado Janeiro 21, 2022 por Ronivaldo Lopes

[Bregnoles]

Boa tarde, Ronivaldo
Essa alteração de configuração no postgres.
Isso pode afetar alguma outra transação do postgres?
Deixando lento em outros processos?

Agurado e

Obrigado!

[Ronivaldo Lopes]

Boa noite,

Respondendo a primeira pergunta, o uso ou não de índices não está relacionado somente com a quantidade de registros. Para que o POSTGRESQL possa selecionar o melhor plano de execução das consultas, é necessário que as tabelas tenham dados estatísticos.

No exemplo abaixo criei uma tabela com 10 milhões de registros e 2 arquivos de índices. O primeiro índice do tipo B-TREE ocupou 214 MB e o segundo do tipo BRIN ocupou somente 32 KB, este segundo índice é usado para BIG DATA.

Executando a primeira consulta do campo id1, o índice teste1 foi utilizado automaticamente, já na segunda consulta utilizando o campo id2, o índice teste2 não foi utilizado, sendo sequencial. Somente após a execução do comando ANALIZE para gerar os dados estatísticos da tabela e uma nova execução, o segundo índice foi utilizado.

create temp table teste as
  select s::int as id1, s::int as id2, quote_ident('Nome ' || to_char(s, 'FM00000000'))::varchar(15) as nome from generate_series(1,10000000) x(s);

create index teste1 on teste (id1);
create index teste2 on teste using brin (id2);

EXPLAIN (analyze on, wal on, verbose on, costs off, format text)
  select id1 from teste where id1 = 1000000;
 
EXPLAIN (analyze on, wal on, verbose on, costs off, format text)
  select id2 from teste where id2 = 1000000;

Analyze VERBOSE teste;

 

 

Editado Março 26, 2022 por Ronivaldo Lopes

[Bregnoles]

Bom dia, Ronivaldo,

Somente uma pergunta: Estamos fazendo uma análise pra melhorar a performance do postgres na utilização de index ou PK nos SELECTS, com todos esses levantamento de teste, qual seria a melhor apção:
1 - enable_indexscan = off e enable_seqscan = on 
ou
2 - enable_indexscan = on e enable_seqscan = off
ou
3 - enablea_indexscan = off e enable_seqscan = off
ou 
4 - enablea_indexscan = on e enable_seqscan = on

Fiz alguns testes com PK e com index e somente com index exemplo:
EXPLAIN ANALYZE
select * from histcomercial, entidades where histcomercial.co_entidades = '182' and entidades.co_categoria = '2';
os dois campos são index, utilizando a opção 1 tive um ganho de 18% na performance, seria então a opção 1 a melhor opção? 

[Bregnoles]

Outra coisa que observei, é que quando você executa pela primeira vez ele na opção 2, o processamento não é tão bom mas depois que ele armazena na memória os próximos processamento melhora muito.

O que você me diz sobre isso?

obrigado Ronivaldo!

[Ronivaldo Lopes]

Boa tarde, por padrão essas opções são on, e como o coletor de estatísticas registra as atividades no banco de dados, o postgresql usa essas estatísticas para definir a melhor execução dos comandos, isso é dinâmico, quando os comandos são executados, as novas estatísticas são feitas. Eu deixo o padrão, mudo para fazer os testes de performance.

Gosto de fazer toda programação dentro do banco e faço uso intensivo de tabelas temporárias. Outra técnica para melhorar a performance, é utilizar parte da memória ram para criar um disco virtual, configurar tablespace temp_pg para uma pasta nesse disco, e usar o temp_pg para criar todas as tabelas e índices temporários, além de poupar a unidade de disco SSD, possui um acesso mais rápido.

[Bregnoles]

Olá Ronivaldo,

Conforme voce falou eu fiz isso:

temp_tablespaces = 'F:/tmp_tablespace/' - alterei parametro do postgres.conf direcionando para outro disco
e rodei esses comandos:

CREATE TABLESPACE dbspace_tmp LOCATION 'F:/tmp_tablespace';
CREATE DATABASE dbspace_tmp tablespace dbspace_tmp;
ALTER DATABASE "my_db" SET temp_tablespaces = dbspace_tmp;

Isso já identifica que vou usar as tabelas temporária do meu banco de dados vão usar essas tabelas temporárias em outro disco. O padrão da tbspc é no local padrão onde está instalado o postgres.
Como esse procedimento ele vai direcionar para essa nova tbspc?
Tem mais alguma coisa a se fazer para melhorar a performance do postgres, voce comentou sobre criar disco virtual utilizando parte da memória RAM, como é feito isso?

Obrigado!

[Ronivaldo Lopes]

Boa noite, existem vários ram disk no mercado, eu uso o ImDisk que é free, configuro a pasta ?:/temp para as variáveis temp e tmp do windows e pasta temporária do postgresql. Uso também para download. No linux pode criar via linha de comando

myramdisk  /tmp/ramdisk  tmpfs  defaults,size=4G,x-gvfs-show  0  0

https://sourceforge.net/projects/imdisk-toolkit/

Você instala e configura só a primeira aba, só mude o tamanho da ram que será utilizada, a letra e definir as variáveis temp, toda vez que a maquina for reiniciada ou ligada, essa unidade de disco virtual será criada (figura abaixo). 

O seu comando CREATE DATABASE está criando todo o banco na pasta F:/tmp_tablespace, tabelas temporárias ou não. Embora você tenha especificado a pasta padrão (tablespace) onde seu banco dbspace_tmp será gravado, você pode especificar um tablespace diferente no comando create table, ou seja, pode usar mais de uma pasta, em discos diferentes para o mesmo banco.

create temp table teste tablespace xxx as
  select s::int as id, ('Nome ' || to_char(s, 'FM0000'))::varchar(15) as nome from generate_series(1,1000) x(s);

Nos comandos abaixo, o postgresql vinculará a pasta d:/temp ao tablespace temp_pg, que criará outra pasta cujo nome inicia com PG_ dentro da pasta d:/temp, no disco virtual. Quando você executa o comando create table, e se verificar dentro da pasta criada, haverá um único arquivo que é a tabela teste, quando você executa o comando create index, aparecerá o segundo arquivo que é o arquivo de índice dentro da mesma pasta, já que a tabela é temporária, o índice também será temporário. Quando executar o drop table teste, os dois arquivos serão excluídos.

CREATE TABLESPACE temp_pg LOCATION 'd:/temp';  

set temp_tablespaces = temp_pg;
  
drop table if exists teste;

create temp table teste as
  select s::int as id, ('Nome ' || to_char(s, 'FM0000000'))::varchar(15) as nome from generate_series(1,1000000) x(s);
  
create index teste1 on teste (id); 

drop table teste;

Sempre trabalhei com SQL Server, ainda estou estudando o postgresql e não sei muito sobre configuração. Mas um bom SSD, memória ram e o uso de um disco virtual faz a diferença. Tenho um sistema no SQL Server, esse servidor utiliza disco rígido e demora uns 3 minutos para executar uma determinada rotina, no postgreql essa mesma rotina executa em 6 segundos utilizando SSD, o meu SSD é de 2,5gb/s e se fosse de 7gb/s seria em um piscar de olhos.

 

Editado Março 26, 2022 por Ronivaldo Lopes

[Bregnoles]

Ronivaldo, 
Boa tarde,

estou querendo atualizar o postgres de 8.3 para 9.3, estou utilizando o pg_upgrade

pg_upgrade.exe

--old-datadir "C:/Arquivos do Programa/PostgreSQL/oldVersion/data"

--new-datadir "C:/Program Files/PostgreSQL/newVersion/data"

--old-bindir "C:/Arquivos do programa/PostgreSQL/oldVersion/bin "

--new-bindir "C:/Program Files/PostgreSQL/newVersion/bin"

- Só que está apresentado esse erro: você poderia me dizer o que eu tenho que fazer para corrigir isso?

 

Erro apresentados.
#mapped win32 error code 2 to 2

Old and new pg_controldata date/time storage types do not match.
#You will need to rebuild the new server with configure option
--disable-integer-datetimes or get server binaries built with those options.

Tradução: Você precisará reconstruir o novo servidor com opção de configuração
-desativar-desabilitar datas de inteiros ou obter binários de servidor construídos com aqueles Opções