From: | Claudio Freire <klaussfreire(at)gmail(dot)com> |
---|---|
To: | Andrew Dunstan <andrew(at)dunslane(dot)net> |
Cc: | AI Rumman <rummandba(at)gmail(dot)com>, postgres performance list <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Why does the number of rows are different in actual and estimated. |
Date: | 2012-12-13 22:42:05 |
Message-ID: | CAGTBQpZrERogVMvzAXouhvq-ERYtSUnR6QUk2EMJren0aB-PNQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Thu, Dec 13, 2012 at 7:36 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> On 12/13/2012 05:12 PM, AI Rumman wrote:
>>
>> Why does the number of rows are different in actual and estimated?
>>
>
>
> Isn't that in the nature of estimates? An estimate is a heuristic guess at
> the number of rows it will find for the given query or part of a query. It's
> not uncommon for estimates to be out by several orders of magnitude.
> Guaranteeing estimates within bounded accuracy and in a given short amount
> of time (you don't want your planning time to overwhelm your execution time)
> isn't possible.
Although this kind of difference could be indeed a problem:
> Nested Loop (cost=18560.97..26864.83 rows=24871 width=535) (actual time=1335.157..8492.414 rows=157953 loops=1)
> -> Hash Left Join (cost=18560.97..26518.91 rows=116 width=454) (actual time=1335.117..6996.585 rows=205418 loops=1)
It usually is due to some unrecognized correlation between the joined tables.
And it looks like it all may be starting to go south here:
> -> Hash Join (cost=9337.97..18115.71 rows=34489 width=244) (actual time=418.054..1156.453 rows=205420 loops=1)
> Hash Cond: (customerdetails.customerid = entity.id)
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Dunstan | 2012-12-13 23:09:42 | Re: Why does the number of rows are different in actual and estimated. |
Previous Message | Evgeny Shishkin | 2012-12-13 22:40:35 | Re: Why does the number of rows are different in actual and estimated. |