Content-Type: text/plain; charset=utf-8; format=flowed; delsp=yes
To: pgsql-performance@postgresql.org, =?utf-8?B?cGFzbWFuIHBhc21hxYRza2k=?=
	<pasman.p@gmail.com>
Subject: Re: Optimizing query
References: <AANLkTimMagbDUWi+UwW=7sspj08gOZxfsGc_EK1ovhfG@mail.gmail.com>
Date: Fri, 26 Nov 2010 10:46:11 +0100
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
From: "Pierre C" <lists@peufeu.com>
Message-ID: <op.vmruq9jseorkce@apollo13>
In-Reply-To: <AANLkTimMagbDUWi+UwW=7sspj08gOZxfsGc_EK1ovhfG@mail.gmail.com>
User-Agent: Opera Mail/10.62 (Linux)


Note that your LEFT JOIN condition is probably quite slow...

Please post EXPLAIN ANALYZE for this simplified version :

SELECT
	R."Osoba weryfikuj?ca",
	R."LP",
	A."NKA",
	A."NTA",
	Sum("Ile")
FROM		"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
	    A."DataPliku" >= current_date-4*30
	and A."KodB??du"=74::text
	and R."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

And also post EXPLAIN ANALYZE for this :

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"