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

On 04/30/2014 01:08 PM, David Noel wrote:
>> 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.
> Some interesting feedback on that query you provided. It took nearly
> 80 seconds to complete.
>
> I rewrote it* as a join and it took .8 seconds to complete:
>
> select p.*, count(*) as NoOfSentences
> from page p
> inner join sentence c on p."URL" = c."URL"
> where "Classification" = 'health'
> group by p."URL"
>
> *I may have written it incorrectly but it does _seem_ to produce correct output.

I must have been very tired when I wrote that.  This latest version of
yours is clearly the way it should be written.

> Something seems odd with laterals. I'll have to dig into it more later
> and report back, I'm not sure it behaves this way.

There is nothing wrong with LATERALs, they just have no business being
used here.  Sorry for the noise.

--
Vik