Very strange. I ran the query and it seemed slow so I rewrote it with
a join instead. Using join it finished in 800ms. The query using the
lateral finished in more than a minute. I guess I need to do some
analysis on those queries to figure out why there was such a vast
difference in performance.
The FreeBSD system is running 9.3, the Windows systems are running
9.2. I am waiting on the output from the other developer.
On Tue, Apr 29, 2014 at 8:46 AM, Tom Lane t...@sss.pgh.pa.us wrote:
David Noel david.i.n...@gmail.com writes:
Both queries are run from a Java project using the latest
For 9.3, you can write that as:
select p.*, s.NoOfSentences
from page p,
lateral (select count(*) as NoOfSentences
from sentence s
where s.PageURL = p.URL) s
where Classification like case ... end
order by PublishDate desc
limit 100;
Performance will be
On 30 Duben 2014, 10:46, David Noel wrote:
Very strange. I ran the query and it seemed slow so I rewrote it with
a join instead. Using join it finished in 800ms. The query using the
lateral finished in more than a minute. I guess I need to do some
analysis on those queries to figure out why
On 04/30/2014 01:08 PM, David Noel wrote:
For 9.3, you can write that as:
select p.*, s.NoOfSentences
from page p,
lateral (select count(*) as NoOfSentences
from sentence s
where s.PageURL = p.URL) s
where Classification like case ... end
order by
There is nothing wrong with LATERALs, they just have no business being
used here. Sorry for the noise.
Ah. No trouble. In fact I'm glad you chimed in -- it motivated me to
learn about laterals so now I know some new SQL syntax!
--
Sent via pgsql-general mailing list
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
On 4/29/14, Achilleas Mantzios ach...@matrix.gatewaynet.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
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
On 4/29/2014 12:42 AM, David Noel wrote:
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.
wild guess says it was
On 04/29/2014 09:44 AM, David Noel wrote:
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
select p.*, s.NoOfSentences
from page p,
lateral (select count(*) as NoOfSentences
from sentence s
where s.PageURL = p.URL) s
where Classification like case ... end
order by PublishDate desc
limit 100;
Great. Thanks so much!
Could I make it even simpler
Ehh, to clarify I'm referring to the lone _double_ quotation mark at
the end of the condition 'health'''. I called it a single quotation
mark because it was a quotation mark all by itself, but realize that
could be misread. Single quotation marks are technically this: '
Sorry for the newbie spam
On 29/04/2014 12:39, David Noel wrote:
Ehh, to clarify I'm referring to the lone _double_ quotation mark at
the end of the condition 'health'''. I called it a single quotation
mark because it was a quotation mark all by itself, but realize that
could be misread. Single quotation marks are
'health''' (if that is what you have) means a boolean expression that
compares the
literal 'health' with the empty literal '' which is of course always false.
Ah. Gotcha. Thanks. I didn't know you could use a single double
quotation mark in a query -- I thought like in most languages that you
On 29/04/2014 12:54, David Noel wrote:
'health''' (if that is what you have) means a boolean expression that
compares the
literal 'health' with the empty literal '' which is of course always false.
Ah. Gotcha. Thanks. I didn't know you could use a single double
quotation mark in a query -- I
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
But there are two of them : ' and ' makes ''. If you use only one
psql/parser will complain.
Ha! Wow. That one totally flew by me. It's not a double quotation mark
(one character), it's a double _single_ quotation mark (two
characters). Yeah, that makes complete sense. Wow. Can't believe I
Is both server/client running on FreeBSD or Windows, or are you switching
only part of the stack?
When I run it it's all FreeBSD. When the other developer working on it
runs it it's all Windows.
It shouldn't get stuck. It might be slower on some platforms, but it
shouldn't really get stuck,
David Noel david.i.n...@gmail.com writes:
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
20 matches
Mail list logo
