From: | Vik Fearing <vik(dot)fearing(at)dalibo(dot)com> |
---|---|
To: | David(dot)I(dot)Noel(at)gmail(dot)com, Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows) |
Date: | 2014-04-29 08:38:44 |
Message-ID: | 535F6514.1080204@dalibo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | basti | 2014-04-29 08:44:53 | WAL Replication + PITR |
Previous Message | John R Pierce | 2014-04-29 07:45:55 | Re: SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows) |