Re: 30-70 seconds query... - Mailing list pgsql-performance

From Tomasz Myrta
Subject Re: 30-70 seconds query...
Date
Msg-id 3E88BE02.9020203@klaster.net
Whole thread Raw
In response to 30-70 seconds query...  ("alexandre :: aldeia digital" <alepaes@aldeiadigital.com.br>)
List pgsql-performance
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


pgsql-performance by date:

Previous
From: "alexandre :: aldeia digital"
Date:
Subject: 30-70 seconds query...
Next
From: Tom Lane
Date:
Subject: Re: 30-70 seconds query...