Very strange. I ran the query and it seemed slow so I rewrote it with a join instead. Using join it finished in 800ms. The query using the lateral finished in more than a minute. I guess I need to do some analysis on those queries to figure out why there was such a vast difference in performance. %10, %20, %50, even %100 differences in performance are huge, but for something to take nearly 100x -- %10000 longer to complete? Something just doesn't seem right.
On Tue, Apr 29, 2014 at 3:38 AM, Vik Fearing <vik.fear...@dalibo.com> wrote: > On 04/29/2014 09:44 AM, David Noel wrote: >> Ahh, sorry, copied the query over incorrectly. It should read as follows: >> >> select page.*, coalesce((select COUNT(*) from sentence where >> sentence."PageURL" = page."URL" group by page."URL"), 0) as >> NoOfSentences from page WHERE "Classification" LIKE CASE WHEN >> 'health'<>'' THEN 'health' ELSE '%' END ORDER BY "PublishDate" DESC >> Offset 0 LIMIT 100 >> >> Does that make any more sense? > > For 9.3, you can write that as: > > select p.*, s.NoOfSentences > from page p, > lateral (select count(*) as NoOfSentences > from sentence s > where s."PageURL" = p."URL") s > where "Classification" like case ... end > order by "PublishDate" desc > limit 100; > > Performance will be much, much better than what you have but it won't > work at all on the 9.2 server. > > -- > Vik > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
