I have the following query and I want to optimize it, the execution time is 4 seconds and the number of rows is 122,928, but the tables do not have anyone link between them.
Is there any ways to optimize this? I need the same number of rows but the time of execution needs to get down considerably.
select dc.cddocument, us.cduser
from dcdocument dc, aduser us
WHERE (dc.FGUSECATACCESSROLE <> 1 OR dc.FGUSECATACCESSROLE IS NULL);
Query Plan
teste20=# explain analyze select dc.cddocument, us.cduser from dcdocument dc, aduser us WHERE (dc.FGUSECATACCESSROLE <> 1 OR dc.FGUSECATACCESSROLE IS NULL);
QUERY PLAN
---------------------------------------------------------------------------------
Nested Loop (cost=0.00..1584.32 rows=124110 width=8) (actual time=0.062..23.679 rows=122928 loops=1)
-> Seq Scan on aduser us (cost=0.00..19.91 rows=591 width=4) (actual time=0.029..0.190 rows=591 loops=1)
-> Materialize (cost=0.00..13.56 rows=210 width=4) (actual time=0.000..0.012 rows=208 loops=591)
-> Seq Scan on dcdocument dc (cost=0.00..12.51 rows=210 width=4) (actual time=0.024..0.308 rows=208 loops=1)
Filter: ((fgusecataccessrole <> 1) OR (fgusecataccessrole IS NULL))
Rows Removed by Filter: 393
Planning time: 0.474 ms
Execution time: 27.183 ms
(8 registros)
The query plan doesn't show the real time from the execution.
CREATE TABLE dcdocument
CREATE TABLE public.dcdocument
(
cddocument integer NOT NULL,
cdcategory integer,
dtdocument date,
fgstatus integer,
dssummary text COLLATE pg_catalog."default",
nmtitle character varying(255) COLLATE pg_catalog."default",
nmauthor character varying(255) COLLATE pg_catalog."default",
iddocument character varying(50) COLLATE pg_catalog."default",
dsdoccancel text COLLATE pg_catalog."default",
fgusecataccessrole integer,
cdcreatedby integer,
nrhits integer,
dtinsert date,
dtupdate date,
nmuserupd character varying(255) COLLATE pg_catalog."default",
dtvalidity date,
qtvalidity integer,
fgtypevalid integer,
cdprod integer,
cdapprov integer,
cdtemparchival integer,
cdfavorite integer,
fgoldstatus integer,
CONSTRAINT pkdcdocument PRIMARY KEY (cddocument),
CONSTRAINT fkdocumcdcreatedby FOREIGN KEY (cdcreatedby)
REFERENCES public.aduser (cduser) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE public.dcdocument
OWNER to postgres;
CREATE INDEX "TESTES"
ON public.dcdocument USING btree
(cddocument, fgusecataccessrole)
TABLESPACE pg_default;
CREATE INDEX seixdoccataccessrole
ON public.dcdocument USING btree
(fgusecataccessrole)
TABLESPACE pg_default;
CREATE INDEX seixdoccdcategory
ON public.dcdocument USING btree
(cdcategory)
TABLESPACE pg_default;
CREATE INDEX seixdocumcdcreated
ON public.dcdocument USING btree
(cdcreatedby)
TABLESPACE pg_default;
CREATE INDEX seixdocumentapprov
ON public.dcdocument USING btree
(cdprod, cdapprov)
TABLESPACE pg_default;
CREATE INDEX seixdocumentfavori
ON public.dcdocument USING btree
(cdfavorite)
TABLESPACE pg_default;
CREATE INDEX seixdocumentstatus
ON public.dcdocument USING btree
(fgstatus)
TABLESPACE pg_default;
CREATE INDEX seixdocumtemparchi
ON public.dcdocument USING btree
(cdtemparchival)
TABLESPACE pg_default;
CREATE TABLE aduser
CREATE TABLE public.aduser
(
cduser integer NOT NULL,
idlogin character varying(50) COLLATE pg_catalog."default",
iduser character varying(50) COLLATE pg_catalog."default",
nmuser character varying(255) COLLATE pg_catalog."default",
idpassword character varying(50) COLLATE pg_catalog."default",
dsuseremail text COLLATE pg_catalog."default",
fglanguage integer,
fguserenabled integer,
cdmailserver integer,
cdleader integer,
dtinsert date,
dtupdate date,
nmuserupd character varying(255) COLLATE pg_catalog."default",
idphone character varying(50) COLLATE pg_catalog."default",
dsuser text COLLATE pg_catalog."default",
nmdomainuid character varying(255) COLLATE pg_catalog."default",
nmcertserialnum character varying(255) COLLATE pg_catalog."default",
flpublickey bytea,
fgnotice integer,
fgstatuslogin integer,
flphoto bytea,
fgaccessrestrict integer,
fgalterpassword integer,
nmviewmodules character varying(255) COLLATE pg_catalog."default",
idpasswordvalid character varying(50) COLLATE pg_catalog."default",
nmuseremail character varying(255) COLLATE pg_catalog."default",
fgtheme integer,
fghomepage integer,
dsurlhomepage text COLLATE pg_catalog."default",
cddashboard integer,
cddashboardtab integer,
fgdashdoshare integer,
dtrequestrstpsw date,
idhashpassword character varying(50) COLLATE pg_catalog."default",
cdlastlicense integer,
fgpwdversion integer,
cdcustomization integer,
nmhashlastsession character varying(255) COLLATE pg_catalog."default",
fgtransfped integer,
cdtransfpedto integer,
fgcontactenable integer,
fgcontactpopup integer,
cdfavorite integer,
fgedittoolbar integer,
fgeditgrid integer,
nmphotopath character varying(255) COLLATE pg_catalog."default",
dtbirthday date,
CONSTRAINT pkaduser PRIMARY KEY (cduser),
CONSTRAINT fk__aduser__cdtransf__56764864 FOREIGN KEY (cdtransfpedto)
REFERENCES public.aduser (cduser) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION,
CONSTRAINT fk_userleader FOREIGN KEY (cdleader)
REFERENCES public.aduser (cduser) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE public.aduser
OWNER to postgres;
CREATE INDEX "TESTE2"
ON public.aduser USING btree
(cduser)
TABLESPACE pg_default;
CREATE INDEX seixcdleader
ON public.aduser USING btree
(cdleader)
TABLESPACE pg_default;
CREATE INDEX seixusercdmailserv
ON public.aduser USING btree
(cdmailserver)
TABLESPACE pg_default;
CREATE INDEX seixusercdtransfpedto
ON public.aduser USING btree
(cdtransfpedto)
TABLESPACE pg_default;
CREATE INDEX seixuserenabled
ON public.aduser USING btree
(fguserenabled)
TABLESPACE pg_default;
CREATE INDEX seixuserfglanguage
ON public.aduser USING btree
(fglanguage)
TABLESPACE pg_default;
CREATE INDEX seixusergnfavorite
ON public.aduser USING btree
(cdfavorite)
TABLESPACE pg_default;
CREATE INDEX seixuseridlogin
ON public.aduser USING btree
(idlogin COLLATE pg_catalog."default")
TABLESPACE pg_default;
CREATE INDEX seixusernmdomainuid
ON public.aduser USING btree
(nmdomainuid COLLATE pg_catalog."default")
TABLESPACE pg_default;
CREATE INDEX seixusernmfgencdus
ON public.aduser USING btree
(nmuser COLLATE pg_catalog."default", iduser COLLATE pg_catalog."default", cduser, fguserenabled)
TABLESPACE pg_default;
Your query is already executed optimally, and it is done in 27 milliseconds.
There are two things that might help to explain the problem you observe:
You perform a cross join between the two tables, because you have no WHERE condition that connects them, that is, every row in aduser is combined with every row in dcdocument that matches the condition.
That leads to 208 * 591 = 122928 result rows, which is probably not what you want.
You must be using a client like pgAdmin that takes a very long time to display the many result rows returned from the query.
The solution is probably to add the missing condition in a WHERE or JOIN clause.