Re: How to read query plan

From: Ragnar Hafstað <gnari(at)simnet(dot)is>
To: Miroslav Šulc <miroslav(dot)sulc(at)startnet(dot)cz>
Cc: PGSQL mailing list <pgsql-performance(at)postgresql(dot)org>
Subject: Re: How to read query plan
Date: 2005-03-13 16:51:04
Message-ID: 1110732664.4659.13.camel@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-performance

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Miroslav Šulc 2005-03-13 17:10:31 Re: How to read query plan
Previous Message John Arbash Meinel 2005-03-13 16:24:14 Re: How to read query plan

Browse pgsql-performance by date

  From Date Subject
Next Message Miroslav Šulc 2005-03-13 17:10:31 Re: How to read query plan
Previous Message John Arbash Meinel 2005-03-13 16:24:14 Re: How to read query plan