On Tue, Aug 9, 2016 at 8:27 PM, Suya Huang <shuang@connexity.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".