pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Miroslav Šulc <miroslav.sulc@startnet.cz>
To: Ragnar Hafstađ <gnari@simnet.is>
Cc: PGSQL mailing list <pgsql-performance@postgresql.org>
Subject: Re: How to read query plan
Date: Sun, 13 Mar 2005 18:23:51 +0100
Message-ID: <42347727.4060109@startnet.cz> (raw)
In-Reply-To: <1110732664.4659.13.camel@localhost.localdomain>
References: <42345D24.8070104@startnet.cz>
	<1110732664.4659.13.camel@localhost.localdomain>

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

Attachments:

miroslav.sulc.vcf (text/x-vcard, 401B) download 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:miroslav.sulc@startnet.cz 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

view thread (37+ messages)  latest in thread

Message-ID: <42347727.4060109@startnet.cz>
Permalink:  ../42347727.4060109@startnet.cz/
Also on:    postgresql.org/message-id/42347727.4060109@startnet.cz

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: miroslav.sulc@startnet.cz, gnari@simnet.is
  Subject: Re: How to read query plan
  In-Reply-To: <42347727.4060109@startnet.cz>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox