Hi Ragnar,
Ragnar Hafstaà wrote:
[snip output of EXPLAIN ANALYZE]
for those of us who have not yet reached the level where one can
infer it from the query plan, how abour showing us the actual
query too ?
I thought it will be sufficient to show me where the main bottleneck is.
And in fact, the query is rather lengthy. But I have included it in the
response to John. So sorry for the incompletness.
but as an example of what to look for, consider the first few lines
(reformatted):
Merge Right Join (cost=9868.84..9997.74 rows=6364 width=815)
(actual time=9982.022..10801.216 rows=6364 loops=1)
Merge Cond: ("outer".idpk = "inner".cadastralunitidfk)
-> Index Scan using cadastralunits_pkey on cadastralunits
(cost=0.00..314.72 rows=13027 width=31)
(actual time=0.457..0.552 rows=63 loops=1)
-> Sort (cost=9868.84..9884.75 rows=6364 width=788)
(actual time=9981.405..10013.708 rows=6364 loops=1)
notice that the index scan is expected to return 13027 rows, but
actually returns 63. this might influence the a choice of plan.
Yes, the situation in this scenario is that the table of CadastralUnits
contains all units from country but the AdDevices in this case are only
from the 63 CadastralUnits. So the result - 63 rows - is just this
little subset. Up to that, not all AdDevices have CadastralUnitIDFK set
to an IDPK that exists in CadastralUnits but to zero (= no CadastralUnit
set).
gnari
Miroslav Åulc
begin:vcard
fn;quoted-printable:Miroslav =C5=A0ulc
n;quoted-printable:=C5=A0ulc;Miroslav
org:StartNet s.r.o.
adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika
email;internet:[EMAIL PROTECTED]
title:CEO
tel;work:+420 257 225 602
tel;cell:+420 603 711 413
x-mozilla-html:TRUE
url:http://www.startnet.cz
version:2.1
end:vcard
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
