Thread: 30-70 seconds query...
Hi all,
I use a case tool and we generate the querys automatically.
The query explained is a part of an Report and takes a long time
to complete (30 ~ 70 seconds). My machine is a Dual Xeon 2 Ghz, 1 Mb DDR,
3 SCSI HW RAID 5.
The tables involved in query have 500.000 rows.
Thank´s for any help...
Alexandre
explain analyze SELECT T2.fi08ufemp, T4.es10almtra, T3.fi08MovEst,
T1.es10qtdgra, T1.es10Tamanh, T1.es10item, T1.es10numdoc, T1.fi08codigo,
T1.es10tipdoc, T1.es10codemp, T4.es10codalm, T4.es10empa, T1.es10datlan,
T4.co13CodPro, T4.co13Emp06, T1.es10EmpTam FROM (((ES10T2 T1 LEFT JOIN
ES10T T2 ON T2.es10codemp = T1.es10codemp AND T2.es10datlan =
T1.es10datlan AND T2.es10tipdoc = T1.es10tipdoc AND T2.fi08codigo =
T1.fi08codigo AND T2.es10numdoc = T1.es10numdoc) LEFT JOIN FI08T T3 ON
T3.fi08ufemp = T2.fi08ufemp AND T3.fi08codigo =T1.fi08codigo) LEFT JOIN
ES10T1 T4 ON T4.es10codemp = T1.es10codemp AND T4.es10datlan =
T1.es10datlan AND T4.es10tipdoc = T1.es10tipdoc AND T4.fi08codigo =
T1.fi08codigo AND T4.es10numdoc = T1.es10numdoc AND T4.es10item =
T1.es10item) WHERE ( T4.co13Emp06 = '1' AND T4.co13CodPro = '16998' AND
T1.es10datlan >= '2003-02-01'::date ) AND ( T1.es10datlan >=
'2003-02-01'::date) AND ( T3.fi08MovEst = 'S' ) AND ( T4.es10empa = '1' OR
( '1' = 0 ) ) AND ( T4.es10codalm = '0' OR T4.es10almtra = '0' OR ( '0'
= 0 ) ) AND ( T1.es10datlan <= '2003-02-28'::date ) ORDER BY
T4.co13Emp06, T4.co13CodPro, T1.es10datlan, T4.es10empa, T4.es10codalm,
T4.es10almtra, T1.es10codemp, T1.es10tipdoc, T1.fi08codigo,
T1.es10numdoc, T1.es10item;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=379749.51..379833.81 rows=33722 width=142) (actual
time=74031.72..74031.72 rows=0 loops=1)
Sort Key: t4.co13emp06, t4.co13codpro, t1.es10datlan, t4.es10empa,
t4.es10codalm, t4.es10almtra, t1.es10codemp, t1.es10tipdoc,
t1.fi08codigo, t1.es10numdoc, t1.es10item
-> Nested Loop (cost=1160.89..377213.38 rows=33722 width=142) (actual
time=74031.18..74031.18 rows=0 loops=1)
Filter: (("inner".co13emp06 = 1::smallint) AND
("inner".co13codpro = 16998) AND ("inner".es10empa =
1::smallint))
-> Hash Join (cost=1160.89..173492.20 rows=33722 width=99)
(actual time=35.98..27046.08 rows=33660 loops=1)
Hash Cond: ("outer".fi08codigo = "inner".fi08codigo)
Join Filter: ("inner".fi08ufemp = "outer".fi08ufemp)
Filter: ("inner".fi08movest = 'S'::bpchar)
-> Hash Join (cost=1120.19..172524.13 rows=33722
width=86) (actual time=33.64..26566.83 rows=33660 loops=1)
Hash Cond: ("outer".es10datlan = "inner".es10datlan)
Join Filter: (("inner".es10codemp =
"outer".es10codemp) AND ("inner".es10tipdoc =
"outer".es10tipdoc) AND ("inner".fi08codigo =
"outer".fi08codigo) AND ("inner".es10numdoc =
"outer".es10numdoc))
-> Index Scan using es10t2_ad1 on es10t2 t1
(cost=0.00..1148.09 rows=33722 width=51) (actual
time=0.08..1885.06 rows=33660 loops=1)
Index Cond: ((es10datlan >= '2003-02-01'::date)
AND (es10datlan <= '2003-02-28'::date))
-> Hash (cost=1109.15..1109.15 rows=4415 width=35)
(actual time=33.23..33.23 rows=0 loops=1)
-> Seq Scan on es10t t2 (cost=0.00..1109.15
rows=4415 width=35) (actual time=0.03..24.63
rows=4395 loops=1)
-> Hash (cost=40.16..40.16 rows=216 width=13) (actual
time=1.91..1.91 rows=0 loops=1)
-> Seq Scan on fi08t t3 (cost=0.00..40.16 rows=216
width=13) (actual time=0.03..1.46 rows=216 loops=1)
-> Index Scan using es10t1_pkey on es10t1 t4 (cost=0.00..6.01
rows=1 width=43) (actual time=1.38..1.39 rows=1 loops=33660)
Index Cond: ((t4.es10codemp = "outer".es10codemp) AND
(t4.es10datlan = "outer".es10datlan) AND (t4.es10tipdoc =
"outer".es10tipdoc) AND (t4.fi08codigo =
"outer".fi08codigo) AND (t4.es10numdoc =
"outer".es10numdoc) AND (t4.es10item = "outer".es10item))
Total runtime: 74032.60 msec
(20 rows)
Uz.ytkownik alexandre :: aldeia digital napisa?:
> Hi all,
>
> I use a case tool and we generate the querys automatically.
> The query explained is a part of an Report and takes a long time
> to complete (30 ~ 70 seconds). My machine is a Dual Xeon 2 Ghz, 1 Mb DDR,
> 3 SCSI HW RAID 5.
> The tables involved in query have 500.000 rows.
>
> Thank´s for any help...
>
> Alexandre
>
>
> explain analyze SELECT T2.fi08ufemp, T4.es10almtra, T3.fi08MovEst,
> T1.es10qtdgra, T1.es10Tamanh, T1.es10item, T1.es10numdoc, T1.fi08codigo,
> T1.es10tipdoc, T1.es10codemp, T4.es10codalm, T4.es10empa, T1.es10datlan,
> T4.co13CodPro, T4.co13Emp06, T1.es10EmpTam FROM (((ES10T2 T1 LEFT JOIN
> ES10T T2 ON T2.es10codemp = T1.es10codemp AND T2.es10datlan =
> T1.es10datlan AND T2.es10tipdoc = T1.es10tipdoc AND T2.fi08codigo =
> T1.fi08codigo AND T2.es10numdoc = T1.es10numdoc) LEFT JOIN FI08T T3 ON
> T3.fi08ufemp = T2.fi08ufemp AND T3.fi08codigo =T1.fi08codigo) LEFT JOIN
> ES10T1 T4 ON T4.es10codemp = T1.es10codemp AND T4.es10datlan =
> T1.es10datlan AND T4.es10tipdoc = T1.es10tipdoc AND T4.fi08codigo =
> T1.fi08codigo AND T4.es10numdoc = T1.es10numdoc AND T4.es10item =
> T1.es10item) WHERE ( T4.co13Emp06 = '1' AND T4.co13CodPro = '16998' AND
> T1.es10datlan >= '2003-02-01'::date ) AND ( T1.es10datlan >=
> '2003-02-01'::date) AND ( T3.fi08MovEst = 'S' ) AND ( T4.es10empa = '1' OR
> ( '1' = 0 ) ) AND ( T4.es10codalm = '0' OR T4.es10almtra = '0' OR ( '0'
> = 0 ) ) AND ( T1.es10datlan <= '2003-02-28'::date ) ORDER BY
> T4.co13Emp06, T4.co13CodPro, T1.es10datlan, T4.es10empa, T4.es10codalm,
> T4.es10almtra, T1.es10codemp, T1.es10tipdoc, T1.fi08codigo,
> T1.es10numdoc, T1.es10item;
>
>
> QUERY PLAN
>
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Sort (cost=379749.51..379833.81 rows=33722 width=142) (actual
> time=74031.72..74031.72 rows=0 loops=1)
> Sort Key: t4.co13emp06, t4.co13codpro, t1.es10datlan, t4.es10empa,
> t4.es10codalm, t4.es10almtra, t1.es10codemp, t1.es10tipdoc,
> t1.fi08codigo, t1.es10numdoc, t1.es10item
> -> Nested Loop (cost=1160.89..377213.38 rows=33722 width=142) (actual
> time=74031.18..74031.18 rows=0 loops=1)
> Filter: (("inner".co13emp06 = 1::smallint) AND
> ("inner".co13codpro = 16998) AND ("inner".es10empa =
> 1::smallint))
> -> Hash Join (cost=1160.89..173492.20 rows=33722 width=99)
> (actual time=35.98..27046.08 rows=33660 loops=1)
> Hash Cond: ("outer".fi08codigo = "inner".fi08codigo)
> Join Filter: ("inner".fi08ufemp = "outer".fi08ufemp)
> Filter: ("inner".fi08movest = 'S'::bpchar)
> -> Hash Join (cost=1120.19..172524.13 rows=33722
> width=86) (actual time=33.64..26566.83 rows=33660 loops=1)
> Hash Cond: ("outer".es10datlan = "inner".es10datlan)
> Join Filter: (("inner".es10codemp =
> "outer".es10codemp) AND ("inner".es10tipdoc =
> "outer".es10tipdoc) AND ("inner".fi08codigo =
> "outer".fi08codigo) AND ("inner".es10numdoc =
> "outer".es10numdoc))
> -> Index Scan using es10t2_ad1 on es10t2 t1
> (cost=0.00..1148.09 rows=33722 width=51) (actual
> time=0.08..1885.06 rows=33660 loops=1)
> Index Cond: ((es10datlan >= '2003-02-01'::date)
> AND (es10datlan <= '2003-02-28'::date))
> -> Hash (cost=1109.15..1109.15 rows=4415 width=35)
> (actual time=33.23..33.23 rows=0 loops=1)
> -> Seq Scan on es10t t2 (cost=0.00..1109.15
> rows=4415 width=35) (actual time=0.03..24.63
> rows=4395 loops=1)
> -> Hash (cost=40.16..40.16 rows=216 width=13) (actual
> time=1.91..1.91 rows=0 loops=1)
> -> Seq Scan on fi08t t3 (cost=0.00..40.16 rows=216
> width=13) (actual time=0.03..1.46 rows=216 loops=1)
> -> Index Scan using es10t1_pkey on es10t1 t4 (cost=0.00..6.01
> rows=1 width=43) (actual time=1.38..1.39 rows=1 loops=33660)
> Index Cond: ((t4.es10codemp = "outer".es10codemp) AND
> (t4.es10datlan = "outer".es10datlan) AND (t4.es10tipdoc =
> "outer".es10tipdoc) AND (t4.fi08codigo =
> "outer".fi08codigo) AND (t4.es10numdoc =
> "outer".es10numdoc) AND (t4.es10item = "outer".es10item))
> Total runtime: 74032.60 msec
> (20 rows)
Is the query below the same to yours?
explain analyze
SELECT T2.fi08ufemp, T4.es10almtra, T3.fi08MovEst,
T1.es10qtdgra, T1.es10Tamanh, T1.es10item, T1.es10numdoc, T1.fi08codigo,
T1.es10tipdoc, T1.es10codemp, T4.es10codalm, T4.es10empa, T1.es10datlan,
T4.co13CodPro, T4.co13Emp06, T1.es10EmpTam
FROM
ES10T2 T1
LEFT JOIN T2 using
(es10codemp,es10datlan,es10tipdoc,fi08codigo,es10numdoc)
LEFT JOIN FI08T T3 using (fi08ufemp,fi08codigo)
LEFT JOIN ES10T1 T4 using
(es10codemp,es10datlan,es10tipdoc,fi08codigo,es10numdoc,es10item)
WHERE ( T4.co13Emp06 = '1' AND T4.co13CodPro = '16998' AND
T1.es10datlan >= '2003-02-01'::date ) AND ( T1.es10datlan >=
'2003-02-01'::date) AND ( T3.fi08MovEst = 'S' ) AND ( T4.es10empa = '1' OR
( '1' = 0 ) ) AND ( T4.es10codalm = '0' OR T4.es10almtra = '0' OR ( '0'
= 0 ) ) AND ( T1.es10datlan <= '2003-02-28'::date )
ORDER BY
T4.co13Emp06, T4.co13CodPro, T1.es10datlan, T4.es10empa, T4.es10codalm,
T4.es10almtra, T1.es10codemp, T1.es10tipdoc, T1.fi08codigo,
T1.es10numdoc, T1.es10item;
I have some ideas for your query:
- you can probably change outer joins into inner ones because of your
where clauses
- it looks like the most selective where clause is on t4. Maybe you
should rewrite your query to have T4 first after "from"?
Check how selective is each your where condition and reorder "from
...tables...." to use your where selectivity.
Regards,
Tomasz Myrta
"alexandre :: aldeia digital" <alepaes@aldeiadigital.com.br> writes:
> I use a case tool and we generate the querys automatically.
> explain analyze SELECT T2.fi08ufemp, T4.es10almtra, T3.fi08MovEst,
> T1.es10qtdgra, T1.es10Tamanh, T1.es10item, T1.es10numdoc, T1.fi08codigo,
> T1.es10tipdoc, T1.es10codemp, T4.es10codalm, T4.es10empa, T1.es10datlan,
> T4.co13CodPro, T4.co13Emp06, T1.es10EmpTam FROM (((ES10T2 T1 LEFT JOIN
> ES10T T2 ON T2.es10codemp = T1.es10codemp AND T2.es10datlan =
> T1.es10datlan AND T2.es10tipdoc = T1.es10tipdoc AND T2.fi08codigo =
> T1.fi08codigo AND T2.es10numdoc = T1.es10numdoc) LEFT JOIN FI08T T3 ON
> T3.fi08ufemp = T2.fi08ufemp AND T3.fi08codigo =T1.fi08codigo) LEFT JOIN
> ES10T1 T4 ON T4.es10codemp = T1.es10codemp AND T4.es10datlan =
> T1.es10datlan AND T4.es10tipdoc = T1.es10tipdoc AND T4.fi08codigo =
> T1.fi08codigo AND T4.es10numdoc = T1.es10numdoc AND T4.es10item =
> T1.es10item) WHERE ( T4.co13Emp06 = '1' AND T4.co13CodPro = '16998' AND
> T1.es10datlan >= '2003-02-01'::date ) AND ( T1.es10datlan >=
> '2003-02-01'::date) AND ( T3.fi08MovEst = 'S' ) AND ( T4.es10empa = '1' OR
> ( '1' = 0 ) ) AND ( T4.es10codalm = '0' OR T4.es10almtra = '0' OR ( '0'
> = 0 ) ) AND ( T1.es10datlan <= '2003-02-28'::date ) ORDER BY
> T4.co13Emp06, T4.co13CodPro, T1.es10datlan, T4.es10empa, T4.es10codalm,
> T4.es10almtra, T1.es10codemp, T1.es10tipdoc, T1.fi08codigo,
> T1.es10numdoc, T1.es10item;
Your CASE tool isn't doing you any favors, is it :-(.
Mostly you need to rearrange the JOIN order into something more efficient.
I'd guess that joining T1 to T4, then to T3, then to T2 would be the
way to go here. Also, some study of the WHERE conditions proves that
all the LEFT JOINs could be reduced to plain joins, because any
null-extended row will get discarded by WHERE anyway. That would be a
good thing to do to give the planner more flexibility.
PG 7.4 will be better prepared to handle this sort of query, but I don't
think it will realize that the T1/T2 left join could be reduced to a
plain join given these conditions (that requires observing that null T2
will lead to null T3 because of the join condition... hmmm, I wonder how
practical that would be...). Without that deduction, the key step of
deciding to join T1/T4 first isn't reachable.
regards, tom lane
I said:
> PG 7.4 will be better prepared to handle this sort of query, but I don't
> think it will realize that the T1/T2 left join could be reduced to a
> plain join given these conditions
I take that back --- actually, the algorithm used in CVS tip *does*
deduce that all these left joins can be plain joins.
Don't suppose you'd like to experiment with a current snapshot to see
how well it does for you?
regards, tom lane
On Mon, 31 Mar 2003, Tom Lane wrote: > I said: > > PG 7.4 will be better prepared to handle this sort of query, but I don't > > think it will realize that the T1/T2 left join could be reduced to a > > plain join given these conditions > > I take that back --- actually, the algorithm used in CVS tip *does* > deduce that all these left joins can be plain joins. > > Don't suppose you'd like to experiment with a current snapshot to see > how well it does for you? Think we can get the authors of the case tool that started this to include it? :-)
Tom, I will try the current snapshot and I will report in the list. Thanks to Tomasz Myrta too for the help. Alexandre > I said: >> PG 7.4 will be better prepared to handle this sort of query, but I >> don't think it will realize that the T1/T2 left join could be reduced >> to a plain join given these conditions > > I take that back --- actually, the algorithm used in CVS tip *does* > deduce that all these left joins can be plain joins. > > Don't suppose you'd like to experiment with a current snapshot to see > how well it does for you? > > regards, tom lane