PostgreSQL query performance and variations

Question

I have an event table with the following columns user_id, points, created. What I need to is to get all points for given month, total points and total rank for given user_id. I need to do it in a single query which resulted in the following attempt:

select
    e.user_id,
    sum(e.points) as total_points,
    (select sum(points) from event where user_id = 1 and extract(month from created) = 1) as month_points,
    (select rank from (select user_id as id, rank() over (order by sum(points) desc) from event group by user_id) as rank where id = 1)
from
    event pte
where
    e.user_id = 1
group by
    e.user_id
;

What I'd like to ask is:

Can this query be slow?
Can it be done better / in an another way?
Is rank function reliable enough or it has been misused?

1) it will be very fast, because it contains an error (in the second subselect) . 2) Yes: correct the error 3) depends on the intention of the query — wildplasser
– wildplasser, Commented May 27, 2015 at 9:04

Radek Postołowicz · Accepted Answer · 2015-05-27 11:23:22Z

1

Assuming postgresql version greater or equal to 9.4, you can use aggregate filter clause to avoid subselects for month_points and rank:

select * from
    (select
        e.user_id,
        sum(e.points) as total_points,
        sum(points) filter (where extract(month from created) = 1) as month_points,
        rank() over (order by sum(points) desc)
    from
        event e
    group by
        e.user_id
    ) as inner_query
where user_id = 1;

answered May 27, 2015 at 11:23

Radek Postołowicz

4,8742 gold badges32 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Opal Over a year ago

Does it make this query to perform better? What if I can't use 9.4?

Radek Postołowicz Over a year ago

filter isn't implemented in earlier pg versions. Performance? It depends: if table event is small or there is no index on user_id my query should perform better (but it's better to check it on your own).

Opal Over a year ago

Thanks. There's and index, it's a foreign key.

user330315 Over a year ago

@Opal: the filter() expression can be replaced with a corresponding sum(case when ... end) expression

Collectives™ on Stack Overflow

PostgreSQL query performance and variations

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related