Marcos
vi sua query , e o sqlrdd não grava espaços a esquerda, ele tira todos os
espaços a direita e grava no banco.
[]s
Luiz
Em 16 de maio de 2013 16:15, Marco Aurelio <marcoprod...@gmail.com>escreveu:
> Acrescentando informações e duvidas. Testando a seguinte query:
>
> SELECT docnumero FROM frete01 WHERE docserie = ' U2' limit 10
>
> Com EXPLAIN vi que o postgresql utiliza SEQSCAN, mas se eu tirar um espaço
> no ' U2' ele passa a usar o indice. Eu tenho um problema com os dados
> desta tabela e estamos trabalhando no programa para corrigir este problema
> do campo ter espaços no inicio do dado, mas seria possivel resolver por
> agora este problema ? Fazer ele usar o indice mesmo tendo este espaço na
> frente do dado no campo ?
>
> Mais uma vez agradeço a atenção recebida.
>
>
> 2013/5/16 Marco Aurelio <marcoprod...@gmail.com>
>
>> Grande Euller,
>>
>> Vamos la, valores das consultas que me pediu:
>> "PostgreSQL 8.1.4 on i386-portbld-freebsd6.1, compiled by GCC cc (GCC)
>> 3.4.4 [FreeBSD] 20050518"
>> "client_encoding";"SQL_ASCII";"session"
>> "client_min_messages";"notice";"session"
>> "DateStyle";"ISO, MDY";"session"
>> "effective_cache_size";"39321";"configuration file"
>> "lc_messages";"C";"configuration file"
>> "lc_monetary";"C";"configuration file"
>> "lc_numeric";"C";"configuration file"
>> "lc_time";"C";"configuration file"
>> "listen_addresses";"*";"configuration file"
>> "log_destination";"stderr";"configuration file"
>> "log_directory";"pg_log";"configuration file"
>> "log_duration";"off";"configuration file"
>> "log_filename";"postgresql-%Y-%m-%d.log";"configuration file"
>> "log_line_prefix";"%t [%p]: [%l-1] user=%u,db=%d ";"configuration file"
>> "log_min_duration_statement";"150";"configuration file"
>> "log_rotation_age";"1440";"configuration file"
>> "log_statement";"none";"configuration file"
>> "maintenance_work_mem";"234905";"configuration file"
>> "max_connections";"150";"configuration file"
>> "max_locks_per_transaction";"256";"configuration file"
>> "random_page_cost";"2";"configuration file"
>> "redirect_stderr";"on";"configuration file"
>> "shared_buffers";"39321";"configuration file"
>> "silent_mode";"on";"configuration file"
>> "TimeZone";"Brazil/East";"command line"
>> "work_mem";"16517";"configuration file"
>>
>> PC: Core I3 4GB de RAM:
>>
>> Definições da tabela e dos indices:
>> CREATE TABLE frete01
>> (
>> ctadebrece character(6),
>> ctacrerece character(6),
>> ctamda character(8),
>> cpf_cgcrem character varying(18),
>> cpf_cgcdes character varying(18),
>> cpf_cgccon character varying(18),
>> cpf_cgcrd character varying(18),
>> cpf_cgcpag character varying(18),
>> tipocarro character(1),
>> codremeten numeric(6,0) DEFAULT 0,
>> coddestina numeric(6,0) DEFAULT 0,
>> coddespach numeric(6,0) DEFAULT 0,
>> codredespa numeric(6,0) DEFAULT 0,
>> codpagador numeric(6,0) DEFAULT 0,
>> codtrajeto numeric(5,0) DEFAULT 0,
>> leitcarga character(1),
>> leitscob character(1),
>> codempresa character(5),
>> docnumero numeric(6,0) DEFAULT 0,
>> docserie character(4),
>> codfatura character(6),
>> seriefatur character(4),
>> codfuncion numeric(5,0) DEFAULT 0,
>> redespacho character(1),
>> redenumero numeric(6,0) DEFAULT 0,
>> redeserie character(4),
>> totalnotas numeric(13,2) DEFAULT 0,
>> datalancam date,
>> datadespac date,
>> dataapagar date,
>> datarecebi date,
>> calculaate numeric(5,0) DEFAULT 0,
>> valorunida numeric(13,2) DEFAULT 0,
>> fretevalor numeric(13,2) DEFAULT 0,
>> seccat numeric(13,2) DEFAULT 0,
>> despacho numeric(13,2) DEFAULT 0,
>> pedagio numeric(13,2) DEFAULT 0,
>> outros numeric(13,2) DEFAULT 0,
>> valorapaga numeric(13,2) DEFAULT 0,
>> valorreceb numeric(13,2) DEFAULT 0,
>> valorrepas numeric(13,2) DEFAULT 0,
>> basecalcul numeric(6,2) DEFAULT 0,
>> aliquota numeric(6,2) DEFAULT 0,
>> icms numeric(13,2) DEFAULT 0,
>> coleta numeric(13,2) DEFAULT 0,
>> ademe numeric(13,2) DEFAULT 0,
>> formapagam character(1),
>> peso numeric(11,3) DEFAULT 0,
>> cubagem numeric(7,0) DEFAULT 0,
>> volumes numeric(4,0) DEFAULT 0,
>> codfilorig numeric(5,0) DEFAULT 0,
>> codfildest numeric(5,0) DEFAULT 0,
>> codfilarre numeric(5,0) DEFAULT 0,
>> observacao character varying(120),
>> codmanifes character(6),
>> seriemanif character(4),
>> veiculo character(8),
>> motorista character(6),
>> natureza character varying(15),
>> tipocarga character(1),
>> descricao character varying(50),
>> databaixa date,
>> codtabela character(4),
>> codtarifa character(3),
>> codtributa character(7),
>> recibonume character(6),
>> reciboseri character(4),
>> hrdespacho character(5),
>> confdesc character(2),
>> rmd character(6),
>> dtconsigna date,
>> dtentrega date,
>> hrentrega character(5),
>> ocorrencia character varying(30),
>> dttickets date,
>> dttickets2 date,
>> dtproducao date,
>> codtabicms character(3),
>> valorfrete numeric(13,5) DEFAULT 0,
>> aliq_subs numeric(5,2) DEFAULT 0,
>> itr numeric(13,5) DEFAULT 0,
>> totalnf numeric(13,2) DEFAULT 0,
>> tptribut character(1),
>> loccolet character varying(20),
>> locentre character varying(20),
>> usucolet character(10),
>> usuentre character(10),
>> veiculo2 character(8),
>> veiculo3 character(8),
>> motorista2 character(6),
>> motorista3 character(6),
>> veicx character(1),
>> loc_imprer boolean,
>> loc_imprec boolean,
>> loc_impred boolean,
>> loc_imprep boolean,
>> stationid character(3),
>> datestamp date,
>> "timestamp" character(8),
>> updcounter numeric(3,0) DEFAULT 0,
>> espcar character varying(15),
>> sr_recno numeric(15,0) NOT NULL DEFAULT nextval('frete01_sq'::regclass),
>> sr_deleted character(1) NOT NULL DEFAULT ' '::bpchar,
>> indkey_001 character varying(254),
>> indkey_002 character varying(254),
>> indkey_003 character varying(254),
>> indkey_004 character varying(254),
>> indkey_005 character varying(254),
>> indkey_006 character varying(254),
>> indkey_007 character varying(254),
>> indkey_008 character varying(254),
>> numcem character(8),
>> numdvf character(8),
>> numrcf character(6),
>> dtlanentr date,
>> codfilial numeric(5,0),
>> desconto numeric(13,2),
>> CONSTRAINT frete01_sr_recno_key UNIQUE (sr_recno),
>> CONSTRAINT frete01_unicod UNIQUE (docserie, docnumero)
>> )
>> WITHOUT OIDS;
>> ALTER TABLE frete01
>> OWNER TO sysadmin;
>>
>> -- Index: frete01_f0101
>>
>> -- DROP INDEX frete01_f0101;
>>
>> CREATE INDEX frete01_f0101
>> ON frete01
>> USING btree
>> (indkey_001);
>>
>> -- Index: frete01_f0102
>>
>> -- DROP INDEX frete01_f0102;
>>
>> CREATE INDEX frete01_f0102
>> ON frete01
>> USING btree
>> (indkey_002);
>>
>> -- Index: frete01_f0103
>>
>> -- DROP INDEX frete01_f0103;
>>
>> CREATE INDEX frete01_f0103
>> ON frete01
>> USING btree
>> (indkey_003);
>>
>> -- Index: frete01_f0104
>>
>> -- DROP INDEX frete01_f0104;
>>
>> CREATE INDEX frete01_f0104
>> ON frete01
>> USING btree
>> (indkey_004);
>>
>> -- Index: frete01_f0105
>>
>> -- DROP INDEX frete01_f0105;
>>
>> CREATE INDEX frete01_f0105
>> ON frete01
>> USING btree
>> (indkey_005);
>>
>> -- Index: frete01_f0106
>>
>> -- DROP INDEX frete01_f0106;
>>
>> CREATE INDEX frete01_f0106
>> ON frete01
>> USING btree
>> (indkey_006);
>>
>> -- Index: frete01_f0107
>>
>> -- DROP INDEX frete01_f0107;
>>
>> CREATE INDEX frete01_f0107
>> ON frete01
>> USING btree
>> (codpagador, sr_recno);
>>
>> -- Index: frete01_f0108
>>
>> -- DROP INDEX frete01_f0108;
>>
>> CREATE INDEX frete01_f0108
>> ON frete01
>> USING btree
>> (codfilarre, sr_recno);
>>
>> -- Index: frete01_f0109
>>
>> -- DROP INDEX frete01_f0109;
>>
>> CREATE INDEX frete01_f0109
>> ON frete01
>> USING btree
>> (cpf_cgcrem, sr_recno);
>>
>> -- Index: frete01_f0110
>>
>> -- DROP INDEX frete01_f0110;
>>
>> CREATE INDEX frete01_f0110
>> ON frete01
>> USING btree
>> (cpf_cgcdes, sr_recno);
>>
>> -- Index: frete01_f0111
>>
>> -- DROP INDEX frete01_f0111;
>>
>> CREATE INDEX frete01_f0111
>> ON frete01
>> USING btree
>> (cpf_cgccon, sr_recno);
>>
>> -- Index: frete01_f0112
>>
>> -- DROP INDEX frete01_f0112;
>>
>> CREATE INDEX frete01_f0112
>> ON frete01
>> USING btree
>> (cpf_cgcrd, sr_recno);
>>
>> -- Index: frete01_f0113
>>
>> -- DROP INDEX frete01_f0113;
>>
>> CREATE INDEX frete01_f0113
>> ON frete01
>> USING btree
>> (cpf_cgcpag, sr_recno);
>>
>> -- Index: frete01_f0114
>>
>> -- DROP INDEX frete01_f0114;
>>
>> CREATE INDEX frete01_f0114
>> ON frete01
>> USING btree
>> (indkey_007);
>>
>> -- Index: frete01_f0115
>>
>> -- DROP INDEX frete01_f0115;
>>
>> CREATE INDEX frete01_f0115
>> ON frete01
>> USING btree
>> (indkey_008);
>>
>> -- Index: frete01_f0116
>>
>> -- DROP INDEX frete01_f0116;
>>
>> CREATE INDEX frete01_f0116
>> ON frete01
>> USING btree
>> (dtentrega, sr_recno);
>>
>> -- Index: frete01_sr
>>
>> -- DROP INDEX frete01_sr;
>>
>> CREATE INDEX frete01_sr
>> ON frete01
>> USING btree
>> (sr_recno);
>>
>> -- Index: frete01_x01
>>
>> -- DROP INDEX frete01_x01;
>>
>> CREATE INDEX frete01_x01
>> ON frete01
>> USING btree
>> (datalancam, docserie, docnumero, sr_recno);
>>
>> -- Index: frete01_x02
>>
>> -- DROP INDEX frete01_x02;
>>
>> CREATE INDEX frete01_x02
>> ON frete01
>> USING btree
>> (datadespac, docserie, docnumero, sr_recno);
>>
>> -- Index: frete01_x03
>>
>> -- DROP INDEX frete01_x03;
>>
>> CREATE INDEX frete01_x03
>> ON frete01
>> USING btree
>> (databaixa, docserie, docnumero, sr_recno);
>>
>> -- Index: frete01_x04
>>
>> -- DROP INDEX frete01_x04;
>>
>> CREATE INDEX frete01_x04
>> ON frete01
>> USING btree
>> (datarecebi, docserie, docnumero, sr_recno);
>>
>> -- Index: frete01_x05
>>
>> -- DROP INDEX frete01_x05;
>>
>> CREATE INDEX frete01_x05
>> ON frete01
>> USING btree
>> (docserie, docnumero, sr_recno);
>>
>> -- Index: frete01_x06
>>
>> -- DROP INDEX frete01_x06;
>>
>> CREATE INDEX frete01_x06
>> ON frete01
>> USING btree
>> (docnumero, docserie, sr_recno);
>>
>> -- Index: frete01_x07
>>
>> -- DROP INDEX frete01_x07;
>>
>> CREATE INDEX frete01_x07
>> ON frete01
>> USING btree
>> (dtlanentr, docserie, docnumero, sr_recno);
>>
>> -- Index: frete01_x08
>>
>> -- DROP INDEX frete01_x08;
>>
>> CREATE INDEX frete01_x08
>> ON frete01
>> USING btree
>> (numdvf, codfilorig);
>>
>> -- Index: frete01_x09
>>
>> -- DROP INDEX frete01_x09;
>>
>> CREATE INDEX frete01_x09
>> ON frete01
>> USING btree
>> (numcem, codfilial);
>>
>> -- Index: frete01_x10
>>
>> -- DROP INDEX frete01_x10;
>>
>> CREATE INDEX frete01_x10
>> ON frete01
>> USING btree
>> (docserie, codfilorig);
>>
>> Executei o ANALYZE na tabela e o resultado ficou:
>> "Limit (cost=0.00..9.75 rows=1 width=23) (actual
>> time=20105.017..20105.017 rows=0 loops=1)"
>> " -> Index Scan Backward using frete01_x01 on frete01
>> (cost=0.00..314633.54 rows=32273 width=23) (actual
>> time=20104.982..20104.982 rows=0 loops=1)"
>> " Index Cond: (docserie = ' U2'::bpchar)"
>> " Filter: (((codfilorig = 94::numeric) OR (codfilorig =
>> 95::numeric) OR (codfilorig = 98::numeric) OR (codfilorig = 99::numeric) OR
>> (codfilorig = 100::numeric) OR (codfilorig = 118::numeric)) AND (datalancam
>> IS NOT NULL))"
>> "Total runtime: 20105.426 ms"
>>
>>
>> Gostaria de saber pq esta escolhendo o indice frete01_x01 ao inves do
>> frete01_x10 que é por docserie + codfilorig.
>>
>> Uma coisa já sei, a versão do postgresql é muita antiga e já estamos
>> planejando a migração, e também estamos revendo a quantidade de indices da
>> tabela, para removermos os desnecessarios.
>>
>> Desde já agradeço a atenção recebida.
>>
>>
>>
>> 2013/5/16 Euler Taveira <eu...@timbira.com.br>
>>
>>> On 16-05-2013 08:44, Marco Aurelio wrote:
>>> > Gostaria da ajuda de vocês para fazer uma determinada query ficar mais
>>> > rápida.
>>> >
>>> Cadê a definição da tabela e dos índices da mesma?
>>>
>>> Você executou um ANALYZE na tabela envolvida antes do EXPLAIN ANALYZE?
>>>
>>> Qual é a versão exata do PostgreSQL?
>>>
>>> Quais os valores modificados no postgresql.conf? Vide a consulta em [1].
>>>
>>>
>>> [1] http://wiki.postgresql.org/wiki/Server_Configuration
>>>
>>>
>>> --
>>> Euler Taveira Timbira - http://www.timbira.com.br/
>>> PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
>>> _______________________________________________
>>> pgbr-geral mailing list
>>> pgbr-geral@listas.postgresql.org.br
>>> https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
>>>
>>
>>
>>
>> --
>> Marco Aurélio Ventura da Silva
>> marcoprod...@gmail.com
>> Prodata Informática e Cadastro LTDA
>> (33)3322-4444
>>
>
>
>
> --
> Marco Aurélio Ventura da Silva
> marcoprod...@gmail.com
> Prodata Informática e Cadastro LTDA
> (33)3322-4444
>
> _______________________________________________
> pgbr-geral mailing list
> pgbr-geral@listas.postgresql.org.br
> https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
>
>
_______________________________________________
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
