Slow queries in postgresql

[Denis Bayramgulov, modificado 9 Anos atrás.]

Hi everyone.
I'm using Liferay Portal Community edition 6.1.1 CE GA 2 with db in postgresql 9.3 server.
And i have troubles with some pages loading speed. In my brouser i see about 6-7 sec waiting for respond from server. I wasn't able to figure out the reason using JProfiler for debug insides JVM. But i have configured slow queries, all queries which are taking more than 1 sec to complete, and every time i'm trying to load my troubled pages, i see same queries in log file. And they duration is 6-7 sec just like the delay from server. There are two types of them:

SELECT JournalArticle.* FROM ( SELECT groupId AS tempGroupId, articleId AS tempArticleId, MAX(version) AS tempVersion FROM JournalArticle WHERE (JournalArticle.companyId = $1) AND (groupId = $2) AND (classNameId = $3) AND ( (articleId LIKE $4 OR $5 IS NULL) AND  (lower(title) LIKE $6 OR $7 IS NULL) AND (description LIKE $8 OR $9 IS NULL) AND (content LIKE $10 OR $11 IS NULL) AND (displayDate >= $12 OR CAST($13 AS TIMESTAMP) IS NULL) AND (displayDate <= $14 OR CAST($15 AS TIMESTAMP) IS NULL) AND (  (reviewDate <= $16 OR CAST($17 AS TIMESTAMP) IS NULL) ) ) GROUP BY groupId, articleId ) TEMP_TABLE INNER JOIN JournalArticle ON (TEMP_TABLE.tempGroupId = JournalArticle.groupId) AND (TEMP_TABLE.tempArticleId = JournalArticle.articleId) AND (TEMP_TABLE.tempVersion = JournalArticle.version) WHERE    (JournalArticle.companyId = $18) ORDER BY JournalArticle.articleId ASC, JournalArticle.version ASC limit $19","parameters: $1 = '10154', $2 = '10180', $3 = '0', $4 = NULL, $5 = NULL, $6 = NULL, $7 = NULL, $8 = NULL, $9 = NULL, $10 = '%2776-03-12-2014%', $11 = '%2776-03-12-2014%', $12 = NULL, $13 = NULL, $14 = NULL, $15 = NULL, $16 = NULL, $17 = NULL, $18 = '10154', $19 = '20'",,,,,,,,""

SELECT COUNT(*) AS COUNT_VALUE FROM ( SELECT groupId AS tempGroupId, articleId AS tempArticleId, MAX(version) AS tempVersion FROM JournalArticle WHERE (JournalArticle.companyId = $1) AND (groupId = $2) AND (classNameId = $3) AND ( (articleId LIKE $4 OR $5 IS NULL) AND  (lower(title) LIKE $6 OR $7 IS NULL) AND (description LIKE $8 OR $9 IS NULL) AND (content LIKE $10 OR $11 IS NULL) AND (displayDate >= $12 OR CAST($13 AS TIMESTAMP) IS NULL) AND (displayDate <= $14 OR CAST($15 AS TIMESTAMP) IS NULL) AND (  (reviewDate <= $16 OR CAST($17 AS TIMESTAMP) IS NULL) ) ) GROUP BY groupId, articleId ) TEMP_TABLE INNER JOIN JournalArticle ON (TEMP_TABLE.tempGroupId = JournalArticle.groupId) AND (TEMP_TABLE.tempArticleId = JournalArticle.articleId) AND (TEMP_TABLE.tempVersion = JournalArticle.version) WHERE    (JournalArticle.companyId = $18)   ","parameters: $1 = '10154', $2 = '10180', $3 = '0', $4 = NULL, $5 = NULL, $6 = NULL, $7 = NULL, $8 = NULL, $9 = NULL, $10 = '%2776-03-12-2014%', $11 = '%2776-03-12-2014%', $12 = NULL, $13 = NULL, $14 = NULL, $15 = NULL, $16 = NULL, $17 = NULL, $18 = '10154'",,,,,,,,""

So it's obviously that every time tomcat tryes to select some data from table JournalArticle in any way it takes bunch of time to complete.
Can you recomend some way to tuning this? Maybe some sort of database querie cache?

[David H Nebinger, modificado 9 Anos atrás.]

Well, you have the sql statements that are taking time... I don't know about postgres, but in a DB like Oracle I could analyze the sql statement and use the results to create one or more indexes to optimize the query...

[Jack Bakker, modificado 9 Anos atrás.]

To get more detail, you can try putting 'explain' or 'explain analyze' in front of the 'select' keyword and then like David says, perhaps add indexes.

Also make sure you have the right postgresql jdbc driver for your Java/Postgresql9.3 http://jdbc.postgresql.org/download.html