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