Ola
eu tenho a seguinte query
select
a.ficha,
a.data,
b.nome,
c.nome
from agenda a, medico b, empresa c
where a.ficha<>0 and ( a.codfun=1 and a.codmed=b.codmed and
a.codcli=c.codcli and a.codcli=b.codcli) order by a.data desc
o explain dela esta me retornando
"Sort (cost=22.25..22.86 rows=244 width=32) (actual time=1.998..2.104
rows=125 loops=1)"
" Sort Key: a.data"
" Sort Method: quicksort Memory: 25kB"
" -> Hash Join (cost=6.47..12.57 rows=244 width=32) (actual
time=1.111..1.754 rows=125 loops=1)"
" Hash Cond: ((a.codmed = b.codmed) AND (a.codcli = c.codcli))"
" -> Seq Scan on agenda a (cost=0.00..3.92 rows=124 width=25)
(actual time=0.116..0.350 rows=125 loops=1)"
" Filter: ((ficha <> 0::numeric) AND (codfun = 1::numeric))"
" -> Hash (cost=5.48..5.48 rows=66 width=42) (actual
time=0.913..0.913 rows=129 loops=1)"
" -> Hash Join (cost=1.02..5.48 rows=66 width=42) (actual
time=0.185..0.674 rows=129 loops=1)"
" Hash Cond: (b.codcli = c.codcli)"
" -> Seq Scan on medico b (cost=0.00..3.31 rows=131
width=24) (actual time=0.013..0.144 rows=131 loops=1)"
" -> Hash (cost=1.01..1.01 rows=1 width=18) (actual
time=0.035..0.035 rows=1 loops=1)"
" -> Seq Scan on empresa c (cost=0.00..1.01
rows=1 width=18) (actual time=0.012..0.015 rows=1 loops=1)"
"Total runtime: 2.356 ms"
Pois essas tabelas foram modeladas como abaixo
CREATE TABLE agenda
(
codcli numeric(4,0) DEFAULT 0,
codmed numeric(4,0) DEFAULT 0,
codfun numeric(10,0) DEFAULT 0,
data date,
hora character(5),
resumo character varying(60),
status character(1),
ficha numeric(15,0) DEFAULT 0,
nrecno numeric(15,0) NOT NULL DEFAULT nextval('agenda_sq'::regclass),
CONSTRAINT codmed_fk1 FOREIGN KEY (codcli, codmed)
REFERENCES medico (codcli, codmed) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT agenda_nrecno_key UNIQUE (nrecno)
)
WITH (
OIDS=FALSE
);
CREATE INDEX agenda_codfun_id
ON agenda
USING btree
(codfun, nrecno);
-- Index: agenda_codmed_id
-- DROP INDEX agenda_codmed_id;
CREATE INDEX agenda_codmed_id
ON agenda
USING btree
(codcli, codmed, nrecno);
-- Index: agenda_ficha_fk
-- DROP INDEX agenda_ficha_fk;
CREATE INDEX agenda_ficha_fk
ON agenda
USING btree
(ficha, nrecno);
-- Index: agenda_sr
-- DROP INDEX agenda_sr;
CREATE INDEX agenda_sr
ON agenda
USING btree
(nrecno);
-- Index: agenda_tg002
-- DROP INDEX agenda_tg002;
CREATE INDEX agenda_tg002
ON agenda
USING btree
(ficha, data, nrecno);
-- Index: agenda_tg003
-- DROP INDEX agenda_tg003;
CREATE INDEX agenda_tg003
ON agenda
USING btree
(codmed, nrecno);
CREATE TABLE empresa
(
codcli numeric(4,0) NOT NULL DEFAULT 0,
nome character varying(40),
endcom character varying(40),
fone character varying(16),
cep character(10),
cidade character varying(20),
bairro character varying(20),
estado character(2),
cgc character varying(18),
inscr character varying(18),
fax character varying(16),
contato character varying(20),
ultloc date,
depto character varying(20),
det date,
codterc character(3),
aliqterc numeric(5,2) DEFAULT 0,
codsat character(10),
aliqacid numeric(5,2) DEFAULT 0,
obs text,
cnae character(7),
abertura date,
ccm character varying(15),
capsoc numeric(15,2) DEFAULT 0,
tipoper character(1),
matriz numeric(3,0) DEFAULT 0,
respons numeric(3,0) DEFAULT 0,
email character varying(60),
hpage character varying(60),
codres numeric(3,0) DEFAULT 0,
nomeres character varying(40),
cpfres character varying(12),
rgres character varying(15),
ufrgres character(2),
nomecon character varying(40),
cpfcon character varying(12),
rgcon character varying(15),
crc character varying(12),
fpas character(3),
porte character(1),
nrecno numeric(15,0) NOT NULL DEFAULT nextval('empresa_sq'::regclass),
CONSTRAINT empresa_nrecno_key UNIQUE (nrecno)
)
WITH (
OIDS=FALSE
);
CREATE INDEX empresa_codcli_id
ON empresa
USING btree
(codcli, nrecno);
-- Index: empresa_sr
-- DROP INDEX empresa_sr;
CREATE INDEX empresa_sr
ON empresa
USING btree
(nrecno);
-- Index: empresa_tg001
-- DROP INDEX empresa_tg001;
CREATE INDEX empresa_tg001
ON empresa
USING btree
(codcli, nrecno);
-- Index: empresa_tg002
-- DROP INDEX empresa_tg002;
CREATE INDEX empresa_tg002
ON empresa
USING btree
(nome, nrecno);
CREATE TABLE medico
(
codcli numeric(4,0) NOT NULL DEFAULT 0,
codmed numeric(4,0) NOT NULL DEFAULT 0,
nome character varying(40),
endereco character varying(60),
bairro character varying(50),
cep character(8),
cidade character varying(50),
estado character(2),
cpf character varying(15),
rg character varying(15),
ufrg character(2),
email character varying(60),
email1 character varying(60),
fone character varying(16),
celular character varying(16),
celular2 character varying(16),
crm character varying(16),
crmest character(2),
nrecno numeric(15,0) NOT NULL DEFAULT nextval('medico_sq'::regclass),
CONSTRAINT medico_pk PRIMARY KEY (codcli, codmed),
CONSTRAINT medico_nrecno_key UNIQUE (nrecno)
)
WITH (
OIDS=FALSE
);
CREATE INDEX medico_codmed_id
ON medico
USING btree
(codcli, codmed, nrecno);
-- Index: medico_sr
-- DROP INDEX medico_sr;
CREATE INDEX medico_sr
ON medico
USING btree
(nrecno);
-- Index: medico_tg001
-- DROP INDEX medico_tg001;
CREATE INDEX medico_tg001
ON medico
USING btree
(codcli, codmed, nrecno);
-- Index: medico_tg002
-- DROP INDEX medico_tg002;
CREATE INDEX medico_tg002
ON medico
USING btree
(nome, nrecno);
-- Index: medico_tg003
-- DROP INDEX medico_tg003;
CREATE INDEX medico_tg003
ON medico
USING btree
(crm, crmest, nrecno);
não era para usar indices?
pois tenho as pk e fks definidas, ou eu esqueci de criar alguma fk
[]s
Luiz
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
