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