Re: what's the slowest part in the SQL

Hi Claudio,

The plan for dog is exactly the same as what’s for cat, thus I didn’t paste them here.

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?

Thanks,
Suya

On 8/10/16, 9:52 AM, "Claudio Freire" <klaussfreire(at)gmail(dot)com> wrote:

On Tue, Aug 9, 2016 at 8:27 PM, Suya Huang <shuang(at)connexity(dot)com> wrote:
> I’ve got a SQL runs for about 4 seconds first time it’s been executed,but
> very fast (20ms) for the consequent runs. I thought it’s because that the
> first time table being loaded into memory. However, if you change the where
> clause value from “cat” to “dog”, it runs about 4 seconds as it’s never been
> executed before. Therefore, it doesn’t sound like the reason of table not
> being cached.
>
>
>
> Can someone explain why it behaves like this? It PG 9.3, I can try
> pg_prewarm to cache both tables by creating the extension (probably need to
> find a 9.4 box and copy those files) if the reason is table not being
> cached.
>
>
>
> From execution plan below, it shows Nested Loop is the slowest part - actual
> time=349.257..4265.928 rows=457 , it’s really slow, for just 457 rows and
> takes 4 seconds!!! But very fast for repetitive runs.
>
>
>
> dev=# explain analyze
>
> SELECT COALESCE(w.displayname, o.name) FROM order o INNER JOIN data w
>
> ON w.name = o.name WHERE (w.name LIKE '%cat%' OR w.displayname LIKE '%cat%')
> AND (NOT w.categories && ARRAY[1, 6, 10, 1337])
>
> ORDER BY o.cnt DESC LIMIT 100;

You're showing the explain for "cat", where the interesting one is
probably "dog".