X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7A61856685 for ; Sun, 13 Mar 2005 17:23:56 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 82862-06 for ; Sun, 13 Mar 2005 17:23:46 +0000 (GMT) Received: from sranda.klamovka.kosire.czf (unknown [82.113.63.66]) by svr1.postgresql.org (Postfix) with ESMTP id 3E1BF5669D for ; Sun, 13 Mar 2005 17:23:45 +0000 (GMT) Received: (qmail 10695 invoked from network); 13 Mar 2005 18:23:45 +0100 Received: from fordfrog.klamovka.kosire.czf (HELO ?192.168.11.101?) (10.15.70.18) by mail.klamovka.kosire.czf with AES256-SHA encrypted SMTP; 13 Mar 2005 18:23:45 +0100 Message-ID: <42347727.4060109@startnet.cz> Date: Sun, 13 Mar 2005 18:23:51 +0100 From: =?UTF-8?B?TWlyb3NsYXYgxaB1bGM=?= Organization: StartNet s.r.o. User-Agent: Mozilla Thunderbird 1.0 (X11/20041208) X-Accept-Language: en-us, en MIME-Version: 1.0 To: =?UTF-8?B?UmFnbmFyIEhhZnN0YcSR?= Cc: PGSQL mailing list Subject: Re: How to read query plan References: <42345D24.8070104@startnet.cz> <1110732664.4659.13.camel@localhost.localdomain> In-Reply-To: <1110732664.4659.13.camel@localhost.localdomain> X-Enigmail-Version: 0.89.5.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/mixed; boundary="------------090701050400070602010807" X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200503/221 X-Sequence-Number: 10926 This is a multi-part message in MIME format. --------------090701050400070602010807 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 --------------090701050400070602010807 Content-Type: text/x-vcard; charset=utf-8; name="miroslav.sulc.vcf" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="miroslav.sulc.vcf" YmVnaW46dmNhcmQNCmZuO3F1b3RlZC1wcmludGFibGU6TWlyb3NsYXYgPUM1PUEwdWxjDQpu O3F1b3RlZC1wcmludGFibGU6PUM1PUEwdWxjO01pcm9zbGF2DQpvcmc6U3RhcnROZXQgcy5y Lm8uDQphZHI7cXVvdGVkLXByaW50YWJsZTtxdW90ZWQtcHJpbnRhYmxlOjs7VnJjaGxpY2s9 QzM9QTlobyAxNjEvNTtQcmFoYSA1OzsxNTAgMDA7PUM0PThDZXNrPUMzPUExIHJlcHVibGlr YQ0KZW1haWw7aW50ZXJuZXQ6bWlyb3NsYXYuc3VsY0BzdGFydG5ldC5jeg0KdGl0bGU6Q0VP DQp0ZWw7d29yazorNDIwIDI1NyAyMjUgNjAyDQp0ZWw7Y2VsbDorNDIwIDYwMyA3MTEgNDEz DQp4LW1vemlsbGEtaHRtbDpUUlVFDQp1cmw6aHR0cDovL3d3dy5zdGFydG5ldC5jeg0KdmVy c2lvbjoyLjENCmVuZDp2Y2FyZA0KDQo= --------------090701050400070602010807--