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 65889565CC for ; Sun, 13 Mar 2005 16:51:07 +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 76879-02 for ; Sun, 13 Mar 2005 16:51:06 +0000 (GMT) Received: from quasar.skima.is (quasar.skima.is [212.30.200.205]) by svr1.postgresql.org (Postfix) with ESMTP id 5D7DD52A93 for ; Sun, 13 Mar 2005 16:51:04 +0000 (GMT) Received: from gnari ([157.157.184.242] [157.157.184.242]) by quasar.skima.is with ESMTP; Sun, 13 Mar 2005 16:51:03 Z Subject: Re: How to read query plan From: Ragnar =?ISO-8859-1?Q?Hafsta=F0?= To: Miroslav =?iso-8859-2?Q?=A9ulc?= Cc: PGSQL mailing list In-Reply-To: <42345D24.8070104@startnet.cz> References: <42345D24.8070104@startnet.cz> Content-Type: text/plain; charset=iso-8859-2 Date: Sun, 13 Mar 2005 16:51:04 +0000 Message-Id: <1110732664.4659.13.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.0.2 Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/218 X-Sequence-Number: 10923 On Sun, 2005-03-13 at 16:32 +0100, Miroslav Šulc wrote: > Hi all, > > I am new to PostgreSQL and query optimizations. We have recently moved > our project from MySQL to PostgreSQL and we are having performance > problem with one of our most often used queries. On MySQL the speed was > sufficient but PostgreSQL chooses time expensive query plan. I would > like to optimize it somehow but the query plan from EXPLAIN ANALYZE is > little bit cryptic to me. > [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 ? 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. gnari