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