From: Ragnar Hafstað <gnari@simnet.is>
To: Miroslav Šulc <miroslav.sulc@startnet.cz>
Cc: PGSQL mailing list <pgsql-performance@postgresql.org>
Subject: Re: How to read query plan
Date: Sun, 13 Mar 2005 16:51:04 +0000
Message-ID: <1110732664.4659.13.camel@localhost.localdomain> (raw)
In-Reply-To: <42345D24.8070104@startnet.cz>
References: <42345D24.8070104@startnet.cz>
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
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: gnari@simnet.is, miroslav.sulc@startnet.cz
Subject: Re: How to read query plan
In-Reply-To: <1110732664.4659.13.camel@localhost.localdomain>
* 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