Re: SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows)

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(dot)fearing(at)dalibo(dot)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
>