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