Re: Optimizing query - Mailing list pgsql-performance

Thanks for reply.


First query:

SELECT
 R."Osoba weryfikująca" AS "Osoba",
 R."LP"::text AS "Sprawa",
 A."NKA",
 A."NTA",
 Sum(A."Ile")::text AS "Ilość CDR"
FROM
  ONLY "NumeryA" A
LEFT JOIN
 "Rejestr stacji do naprawy" R
ON
 A."NKA" = R."Numer kierunkowy"
 and A."NTA" like R."Numer stacji"
 and substr(A."NTA",1,5) = substr(R."Numer stacji",1,5)

WHERE
  "DataPliku" >= current_date-4*30
  and "KodBłędu"=74::text
  and "Data weryfikacji" >= current_date-4*30

GROUP BY R."Osoba weryfikująca",R."LP",A."NKA", A."NTA"
ORDER BY Sum("Ile") DESC
LIMIT 5000

-----------------------
Explain analyze
-----------------------

"Limit  (cost=8806.28..8806.30 rows=5 width=28) (actual
time=2575.143..2607.092 rows=5000 loops=1)"
"  ->  Sort  (cost=8806.28..8806.30 rows=5 width=28) (actual
time=2575.135..2586.797 rows=5000 loops=1)"
"        Sort Key: (sum(a."Ile"))"
"        Sort Method:  quicksort  Memory: 929kB"
"        ->  HashAggregate  (cost=8806.12..8806.23 rows=5 width=28)
(actual time=2500.549..2544.315 rows=9564 loops=1)"
"              ->  Merge Join  (cost=8196.81..8806.04 rows=5 width=28)
(actual time=1583.222..2368.858 rows=37364 loops=1)"
"                    Merge Cond: (((a."NKA")::text = (r."Numer
kierunkowy")::text) AND ((substr((a."NTA")::text, 1, 5)) =
(substr((r."Numer stacji")::text, 1, 5))))"
"                    Join Filter: ((a."NTA")::text ~~ (r."Numer stacji")::text)"
"                    ->  Sort  (cost=5883.01..5952.95 rows=27977
width=15) (actual time=1006.220..1118.692 rows=46769 loops=1)"
"                          Sort Key: a."NKA", (substr((a."NTA")::text, 1, 5))"
"                          Sort Method:  quicksort  Memory: 4313kB"
"                          ->  Bitmap Heap Scan on "NumeryA" a
(cost=1454.33..3816.64 rows=27977 width=15) (actual
time=16.331..158.007 rows=46769 loops=1)"
"                                Recheck Cond: (("DataPliku" >=
(('now'::text)::date - 120)) AND (("KodBłędu")::text = '74'::text))"
"                                ->  Bitmap Index Scan on dp_kb
(cost=0.00..1447.34 rows=27977 width=0) (actual time=15.838..15.838
rows=46769 loops=1)"
"                                      Index Cond: (("DataPliku" >=
(('now'::text)::date - 120)) AND (("KodBłędu")::text = '74'::text))"
"                    ->  Sort  (cost=2313.79..2364.81 rows=20410
width=24) (actual time=576.966..703.179 rows=56866 loops=1)"
"                          Sort Key: r."Numer kierunkowy",
(substr((r."Numer stacji")::text, 1, 5))"
"                          Sort Method:  quicksort  Memory: 1973kB"
"                          ->  Seq Scan on "Rejestr stacji do naprawy"
r  (cost=0.00..852.74 rows=20410 width=24) (actual time=0.050..143.901
rows=20768 loops=1)"
"                                Filter: ("Data weryfikacji" >=
(('now'::text)::date - 120))"
"Total runtime: 2620.220 ms"

---------------------------
Second query:
----------------------------
SELECT
    A."NKA",
    A."NTA",
    Sum("Ile") AS ss -- if it's in this table
FROM        "NumeryA" A
WHERE
        A."DataPliku" >= current_date-4*30
    and A."KodBłędu"=74::text
GROUP BY A."NKA", A."NTA"

--------------------------------
Explain analyze:
--------------------------------

"HashAggregate  (cost=20616.64..20643.22 rows=2798 width=15) (actual
time=13244.712..13284.490 rows=14288 loops=1)"
"  ->  Append  (cost=1454.33..20406.79 rows=27979 width=15) (actual
time=16.811..13093.395 rows=46769 loops=1)"
"        ->  Bitmap Heap Scan on "NumeryA" a  (cost=1454.33..3816.64
rows=27977 width=15) (actual time=16.804..141.495 rows=46769 loops=1)"
"              Recheck Cond: (("DataPliku" >= (('now'::text)::date -
120)) AND (("KodBłędu")::text = '74'::text))"
"              ->  Bitmap Index Scan on dp_kb  (cost=0.00..1447.34
rows=27977 width=0) (actual time=16.289..16.289 rows=46769 loops=1)"
"                    Index Cond: (("DataPliku" >= (('now'::text)::date
- 120)) AND (("KodBłędu")::text = '74'::text))"
"        ->  Seq Scan on "NumeryA_2008" a  (cost=0.00..16590.16 rows=2
width=15) (actual time=12759.731..12759.731 rows=0 loops=1)"
"              Filter: ((("KodBłędu")::text = '74'::text) AND
("DataPliku" >= (('now'::text)::date - 120)))"
"Total runtime: 13314.149 ms"


The first query looks to work faster than original (6s) thanks !!! :)



------------
pasman