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
