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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
