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
