query optimization help

[NogDog]

The current query, which is too slow (can take over 10 seconds):

SELECT
	rev.review_id AS review_id,
	rev.review_timestamp AS review_timestamp,
	(
		SELECT COUNT(*)
		FROM foo_bar.review_like
		WHERE rev.review_id = review_id
	) AS likes,
	(
		SELECT COUNT(*)
		FROM foo_bar.review_dislike
		WHERE rev.review_id = review_id
	) AS dislikes,
	(
		SELECT COUNT(*)
		FROM foo_bar.review_flag
		WHERE rev.review_id = review_id
	) AS flags
FROM
	foo_bar.review AS rev
WHERE
	rev.predicate_id = 'some value'
AND
	rev.status = 'A'
GROUP BY
	rev.review_id,
	review_timestamp
ORDER BY
	rev.review_timestamp desc
LIMIT 99

Table review has the main data, with tables review_like, review_dislike, and review_flag relating to review by its primary key. (There may be 0-n matches in each of those related tables.) We simply want a count of all likes, dislikes, and flags for all matching reviews, even those excluded by the LIMIT clause. Anyone have a better solution?

I tried something like the following, but it came up with every count being the sum of all likes, dislikes, and flags for all reviews (instead of just the totals for each specific review):

SELECT
	rev.review_id AS review_id,
	rev.review_timestamp AS review_timestamp,
	COUNT(rl.review_id) AS likes,
	COUNT(rd.review_id) AS dislikes,
	COUNT(rf.review_id) AS flags
FROM foo_bar.review AS rev
	INNER JOIN foo_bar.review_like    AS rl ON rl.review_id = rev.review_id
	INNER JOIN foo_bar.review_dislike AS rd ON rd.review_id = rev.review_id
	INNER JOIN foo_bar.review_flag    AS rf ON rf.review_id = rev.review_id
WHERE
	rev.predicate_id = 'some value'
AND
	rev.status = 'A'
GROUP BY
	rev.review_id,
	review_timestamp
ORDER BY
	rev.review_timestamp desc
LIMIT 99

[johanafm]

Why not create triggers after insert for likes and dislikes to update review: likes= likes + 1 and dislikes = dislikes + 1 respectively. That way you will always have an updated count in the main relation?

Then you will only need to join review_flags.

[johanafm]

Obviously you'd need triggers after delete as well.

[NogDog]

Interesting idea. I'll run it up the flag pole here and see what the experts think.

[tomhath]

I don't see that the GROUP BY in your main query is doing anything. Try doing normal JOINS instead of those subselects which are really expensive.

Untested but should be close:

SELECT
	rev.review_id AS review_id,
	rev.review_timestamp AS review_timestamp,
	like_count,
	dislike_count,
	flag_count
FROM
	foo_bar.review AS rev
LEFT JOIN (
		SELECT review_id, COUNT(*) as like_count
		FROM foo_bar.review_like
		group by review_id
	)  as likes
  on likes.review_id = rev.review_id
LEFT JOIN (
		SELECT review_id, COUNT(*) as dislike_count
		FROM foo_bar.review_dislike
		group by review_id
	)  as dislikes
  on dislikes.review_id = rev.review_id
LEFT JOIN (
		SELECT review_id, COUNT(*) as flag_count
		FROM foo_bar.review_flag
		group by review_id
	)  as flags
  on flags.review_id = rev.review_id
WHERE
	rev.predicate_id = 'some value'
AND
	rev.status = 'A'
ORDER BY
	rev.review_timestamp desc
LIMIT 99

[NogDog]

tomhath;10995386 wrote:

I don't see that the GROUP BY in your main query is doing anything. Try doing normal JOINS instead of those subselects which are really expensive.

Untested but should be close...

Thanks for the suggestion. I'll play around with it Monday and see how it works.

[niroshan]

Hi NogDog,

I don`t understand why do you need review_like, review_dislike, review_flag tables in the first place? You simply wants to kept them to track no of likes, dislikes and flags right?

If thats the case why dont you have 3 fields in the [B]review[/B] table it self. So when ever user like, dislike or flag increment the counter by one. This way youll have all the data in one table.

P.S:
The idea here is only to keep track of the number. If you`r storing more information when user click on like, dislike or flag (name, email ...etc) then this idea wont work since then you need separate 3 tables for that.

Just an idea 🙂

Thanks,
Best regards,
niroshan

[NogDog]

niroshan;10995497 wrote:

Hi NogDog,

I don`t understand why do you need review_like, review_dislike, review_flag tables in the first place? You simply wants to kept them to track no of likes, dislikes and flags right?

If thats the case why dont you have 3 fields in the [B]review[/B] table it self. So when ever user like, dislike or flag increment the counter by one. This way youll have all the data in one table.

P.S:
The idea here is only to keep track of the number. If you`r storing more information when user click on like, dislike or flag (name, email ...etc) then this idea wont work since then you need separate 3 tables for that.

Just an idea 🙂

Thanks,
Best regards,
niroshan

Because each like/dislike/flag gets recorded with associated info (user_id, timestamp), mainly so that a given user cannot flag or like/dislike the same review multiple times.

[niroshan]

Hi NogDog,

In that case I have to agree with johanafm 😃. I`m still testing out new ways to achieve this. will post if i found something smart 🙂

Best regards,
niroshan

[NogDog]

tomhath;10995386 wrote:

I don't see that the GROUP BY in your main query is doing anything. Try doing normal JOINS instead of those subselects which are really expensive.

Untested but should be close:
...

Functioned fine, but only showed maybe a 10% performance improvement for my test query. Well, I shouldn't say "only", as that sounds unappreciative -- any improvement is good. 🙂 But I'm wondering if the postgreSQL log that flagged this query was just an outlier situation, as most of my test runs are coming in under 0.3 seconds with the worst just over 1 second (the log was saying 10.5 seconds).

[Derokorian]

I still think triggers to increment / decrement a counter column is the best way to go. Maybe even a cron job to do a count on each type and make sure the counter value matches once a day or week or something. (In case the trigger failed for some reason)

[tomhath]

Keeping running totals would certainly work. But this is a pretty simple query, the kind of thing databases do really well. Even on a database with millions of rows it should run almost instantly (assuming proper indexes, etc).

Maybe the 10 second query was an anomaly, that happens. Look at the connection pool size, memory usage, and all the other usual things that can slow a normally fast query down.

[sneakyimp]

NogDog;10995505 wrote:

Functioned fine, but only showed maybe a 10% performance improvement for my test query. Well, I shouldn't say "only", as that sounds unappreciative -- any improvement is good. 🙂 But I'm wondering if the postgreSQL log that flagged this query was just an outlier situation, as most of my test runs are coming in under 0.3 seconds with the worst just over 1 second (the log was saying 10.5 seconds).

How may records are in these tables? I have found that subqueries can start to really slow things down when you deal with large tables -- creating temporary tables, etc. You may find that if you have a really large number of records that these subqueries start to get really expensive. Or maybe not. Maybe try generating large amounts of dummy data and trying it then? The docs have a pretty detailed discussion that may or may not be appropriate to your situation.

I too would recommend implementation of a like counter somewhere. You might still have to store associations between reviews and users and likes/dislikes/flags so that you know who can unlike/undislike/unflag stuff, but to add up all the likes and dislikes each time will probably start to bite you in the ass when you have millions of records.

[NogDog]

As is usual, priorities are shifting day by day at work, so I may not get to address this right away. Or, priorities may shift and I'll be addressing it tomorrow. 🙂