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

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).


Miroslav Åulc
fn;quoted-printable:Miroslav =C5=A0ulc
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]
tel;work:+420 257 225 602
tel;cell:+420 603 711 413

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Reply via email to