From: | Evgeny Shishkin <itparanoia(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Andrew Dunstan <andrew(at)dunslane(dot)net>, Claudio Freire <klaussfreire(at)gmail(dot)com>, 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 23:50:19 |
Message-ID: | 66FFC2DE-017B-4BD7-9E69-6A9982FD4A0E@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Dec 14, 2012, at 3:36 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Evgeny Shishkin <itparanoia(at)gmail(dot)com> writes:
>> On Dec 14, 2012, at 3:09 AM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>>> Well, it looks like it's choosing a join order that's quite a bit different from the way the query is expressed, so the OP might need to play around with forcing the join order some.
>
>> OP joins 8 tables, and i suppose join collapse limit is set to default 8. I thought postgresql's optimiser is not mysql's.
>
> It's not obvious to me that there's anything very wrong with the plan.
> An 8-way join that produces 150K rows is unlikely to run in milliseconds
> no matter what the plan. The planner would possibly have done the last
> join step differently if it had had a better rowcount estimate, but even
> if that were free the query would still have been 7 seconds (vs 8.5).
>
May be in this case it is. I once wrote to this list regarding similar problem - joining 4 tables, result set are off by 2257 times - 750ms vs less then 1ms. Unfortunately the question was not accepted to the list.
I spoke to Bruce Momjian about that problem on one local conference, he said shit happens :)
> regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Huan Ruan | 2012-12-13 23:51:27 | Re: hash join vs nested loop join |
Previous Message | Tom Lane | 2012-12-13 23:36:54 | Re: Why does the number of rows are different in actual and estimated. |