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

On 29 Duben 2014, 8: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"
>
> 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.

Is both server/client running on FreeBSD or Windows, or are you switching
only part of the stack?

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

It shouldn't get stuck. It might be slower on some platforms, but it
shouldn't really get stuck, so it might be a bug.

On linux I'd recommend perf/strace/... to investigate the issue, but I'm
not familiar with similar tool on Windows.

Is the query eating a lot of CPU, or is it just sitting there idle, doing
nothing? Or is there some other process doing a lot of CPU (e.g. Java)?

Can you try running the query through 'psql' directly, to rule out JDBC
issues etc.? Try to collect explain plans for the query (maybe there's
something wrong with it).

Tomas