Re: SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows) - Mailing list pgsql-general

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