On 29/04/2014 09:59, David Noel wrote:
> The query I'm running is:
>
> "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 "<>"
> THEN " ELSE '%' END ORDER BY "PublishDate" DESC Offset 0 LIMIT 100"
In all honesty, this query is very badly written. It seems like it was ported from some other
system. The inner group by in the coalesce is redundant since the result is always one row,
moreover, it is wrong since coalesce accepts a scalar value, it hits the eye at first sight.
Additionally, ''<>'' always returns false, what's the purpose of the CASE statement?
>
> I can post the table definitions if that would be helpful but I don't
> have them on hand at the moment.
>
> The gist of it though is that "page" and "sentence" are two tables.
> page.URL maps to sentence.PageURL. The page table has the columns
> "Classification", and "PublishDate". URL, PageURL, and Classification
> are strings. PublishDate is a timestamp with timezone.
>
> Both queries are run from a Java project using the latest JDBC driver.
> The PostgreSQL Server versions it's being run on are 9.2 and 9.3. The
> query executes and returns just fine when run on a FreeBSD-based
> platform, but executes forever when run under Windows.
>
> Does anyone have any idea why this might be happening? Are there
> platform/syntax compatibility issues I'm triggering here that I'm
> unaware of? Is there something wrong with the query?
>
> We're going to try to test it under Linux too, but that system will
> have to be set up first so it might be a while before we know those
> results.
>
> Any thoughts would be appreciated,
Try to re-write the query in a good form, and then perform EXPLAIN ANALYZE on both systems
to see what's wrong.
>
> David Noel
>
>
--
Achilleas Mantzios
Head of IT DEV
IT DEPT
Dynacom Tankers Mgmt
