Re: SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows) - Mailing list pgsql-general

> 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.

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.

For the record, with modification the query you provided wound up
getting executed looking like this:

select p.*, s.NoOfSentences
from page p,
     lateral (select count(*) as NoOfSentences
              from sentence s
              where s."PageURL" = p."URL") s
where "Classification" = 'health'
order by "PublishDate" desc
limit 100;