From: | David Noel <david(dot)i(dot)noel(at)gmail(dot)com> |
---|---|
To: | Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows) |
Date: | 2014-04-29 07:44:53 |
Message-ID: | CAHAXwYC=sQ0k6Cgj886jS37-zGzZ=h1-fOHeVs3qwtMQb0QqSg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)i(dot)noel(at)gmail(dot)com> wrote:
> On 4/29/14, Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> 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.
>
From | Date | Subject | |
---|---|---|---|
Next Message | John R Pierce | 2014-04-29 07:45:55 | Re: SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows) |
Previous Message | David Noel | 2014-04-29 07:42:36 | Re: SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows) |