pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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






view thread (37+ messages)  latest in thread

Message-ID: <1110732664.4659.13.camel@localhost.localdomain>
Permalink:  ../1110732664.4659.13.camel@localhost.localdomain/
Also on:    postgresql.org/message-id/1110732664.4659.13.camel@localhost.localdomain

 · 

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: 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