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
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
