Re: what's the slowest part in the SQL

On Tue, Aug 9, 2016 at 9:12 PM, Suya Huang <shuang(at)connexity(dot)com> wrote:
> Hi Claudio,
>
> The plan for dog is exactly the same as what’s for cat, thus I didn’t paste them here.

Are you sure?

The plan itself may be the same, but the numbers may be different, and
in fact be key to understanding the problem.

>
> Richard Albright just pointed that it’s because the result has been cached not the table, I think that makes sense. So my question changes to the efficiency of NESTED LOOP JOIN, 400 rows for 4 seconds, sounds slow to me. Is that normal?

From the looks of those timing numbers, everything involving reads
from disk is slower on the first run. That clearly points to disk
cache effects. So this explain looks completely normal.

If the query for "dog" doesn't get a speedup on second runs, it could
just be that the data it visits doesn't fit in disk cache, so the
numbers are important, they can tell you that.