CODEWITHSUNDEEP
postgresql
Arch1tect
Arch1tect

Reputation: 4281

View doesn't increase performance of correlated subquery?

I have table User and table Order, now I want to find the names of user who made more than 100 orders, I can do a query like below:

SELECT U.name
FROM User U
WHERE 100 < (
SELECT COUNT(*) FROM Orders O
WHERE O.uid=U.uid
)

This is slow because of correlated subquery.

Therefore I think I can optimize it by creating a view which contains how many order each user has made like below

View UserOrderCount

uid    orderCount
0       11
1       108
2       100
3       99
4       32
5       67

Then the query is much simpler:

SELECT U.name
FROM User U, UserOrderCount C
WHERE 100 < C.orderCount And U.uid=C.cid;

But this turn out to take more time, I can't figure out why... Please shed some light on this, thanks in advance!

EDIT:

Here is how the view is created:

CREATE VIEW UserOrderCount
AS
select U.uid, count(*) AS orderCount
from User U, orders O
group by U.uid;

Upvotes: 0

Views: 112

Answers (2)

jjanes
jjanes

Reputation: 44227

Creating a view would not be expected to make this faster. The same amount of work still needs to be done behind the scenes. The view just makes the text of your query simpler to look at; it doesn't make it simpler to execute. (But note that you are using two different queries, one is a subquery and one is a join. This is orthogonal to the use of a view: you could have used the view in a subquery, or you could have done a join without the view. Mixing these two things together is likely to cause confusion.)

With Materialized Views, new in 9.3, it would actually store the computed results of the counting, so the execution would be faster. But the price you pay for this is that you would need to refresh the materialized view periodically, and you would be using out-of-date counts in the mean time.

Upvotes: 1

jcalfee314
jcalfee314

Reputation: 4830

SELECT U.name,count(*)
FROM User U
JOIN Orders o on o.uid=u.uid
WHERE COUNT(*) > 100
GROUP BY U.NAME

You do not need a view for that...

Upvotes: 0

Related Questions