Caro Luiz, a versão que tenho do SQLRDD é um pouco antiga, e grava os espaços a esquerda.
Em 17 de maio de 2013 05:45, Luiz Rafael Culik <[email protected]>escreveu: > 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 <[email protected]>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 <[email protected]> >> >>> 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 <[email protected]> >>> >>>> 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 >>>> [email protected] >>>> https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral >>>> >>> >>> >>> >>> -- >>> Marco Aurélio Ventura da Silva >>> [email protected] >>> Prodata Informática e Cadastro LTDA >>> (33)3322-4444 >>> >> >> >> >> -- >> Marco Aurélio Ventura da Silva >> [email protected] >> Prodata Informática e Cadastro LTDA >> (33)3322-4444 >> >> _______________________________________________ >> pgbr-geral mailing list >> [email protected] >> https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral >> >> > > _______________________________________________ > pgbr-geral mailing list > [email protected] > https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral > > -- Marco Aurélio Ventura da Silva [email protected] Prodata Informática e Cadastro LTDA (33)3322-4444
_______________________________________________ pgbr-geral mailing list [email protected] https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
