On Tue, Aug 9, 2016 at 8:27 PM, Suya Huang <shuang@xxxxxxxxxxxxx> 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". -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
