Ahh, sorry, copied the query over incorrectly. It should read as follows: 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 'health'<>'' THEN 'health' ELSE '%' END ORDER BY "PublishDate" DESC Offset 0 LIMIT 100 Does that make any more sense? On 4/29/14, David Noel <david.i.noel@xxxxxxxxx> wrote: > On 4/29/14, Achilleas Mantzios <achill@xxxxxxxxxxxxxxxxxxxxx> wrote: >> On 29/04/2014 09:59, David Noel wrote: >>> "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? > > Ok, thanks for the heads up. It confused me, too. It's code I'm just > picking up from another developer, so I don't know why it was done the > way it was done. I'm not super proficient with SQL but I'll take a > stab at rewriting it. > >> Try to re-write the query in a good form, and then perform EXPLAIN >> ANALYZE >> on both systems to see what's wrong. > > Will do. Thanks for the advice. > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
