CODEWITHSUNDEEP
sqlpostgresql
Radek Postołowicz
Radek Postołowicz

Reputation: 4774

Aggregate function with limit and without full table scan

I have photo table:

create table photo(
    id integer,
    ...
    user_id integer,
    created_at date
);

I'd like to achieve the same result as:

select 
    json_agg(photo), 
    created_at,
    id_user
from photo
group by created_at, id_user
order by created_at desc, id_user 
limit 5;

but avoiding full table scan on photo.

Is it possible? I was thinking of recursive CTE but I couldn't manage to construct it.

Upvotes: 1

Views: 958

Answers (3)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125254

If there is an index on created_at and it is possible to suppose there are at least 5 photos in the last 24 hours (or 48 or whatever) it is possible to avoid the full scan:

select 
    json_agg(photo), 
    created_at,
    id_user
from photo
where created_at > (select max(created_at) from photo) - interval '24 hours'
group by created_at, id_user
order by created_at desc, id_user 
limit 5;

The shorter the interval the shorter the scan.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269843

Assuming you have an index on photo(id_user, created_at), then you can select the five rows that you want using a subquery. Then use a join or correlated subquery to get the rest of the information:

select cu.created_at, cu.id_user,
       (select json_agg(p.photo)
        from photo p
        where cu.created_at = p.created_at and cu.id_user = p.id_user
       )
from (select distinct created_at, id_user
      from photo p
      order by created_at desc, id_user
      limit 5
     ) cu
order by cu.created_at desc, cu.id_user ;

Upvotes: 4

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Not recursive, You can try with a single CTE to see if get the TOP 5 without full scan

WITH cte as (
  SELECT DISTINCT created_at, id_user
  FROM photo
  ORDER BY created_at DESC, id_user
  LIMIT 5
)
SELECT p.created_at, p.id_user, json_agg(p.photo)
FROM photo p
JOIN cte c
  ON p.created_at = c.created_at 
 AND p.id_user = c.id_user
GROUP BY p.created_at, p.id_user
ORDER BY p.created_at DESC, p.id_user

Upvotes: 1

Related Questions