3

Let's say I have a simple table in Postgres (11.3):

create table posts
(
    id serial not null,
    created_at timestamp(0)
    constraint posts_pkey
        primary key (id)
);

If a user requests id=5869, I need to be able to return the N rows before and the N rows after that row in a query that is ordered by the created_at column. If we're able to assume that the larger the id, the larger the created_at, we can do something relatively simple like this:

(select * from posts where id < 5869 order by id limit 10)
union all
(select * from posts where id >= 5869 order by id limit 11);

However, I am unable to assume that the higher id is most recently created and I'm wondering what the best way to retrieve that data is in that case. This method works, but is remarkably slow on a 100k row dataset:

WITH 
  boundaries AS (
    SELECT *,
           row_number() OVER (ORDER BY created_at DESC) AS rownum
    FROM posts
  ),
  target_boundary AS (
     SELECT *
     FROM boundaries
     WHERE boundaries.id = 5869
 )
SELECT posts.*, boundaries.rownum
FROM posts
LEFT JOIN boundaries ON posts.id = boundaries.id
JOIN target_boundary ON boundaries.rownum BETWEEN target_boundary.rownum - 10 AND target_boundary.rownum + 10

Running through that was taking upwards of 800 milliseconds which is far too slow on a dataset so small.

I have also tried a variation of the above using lead() and lag(), but that was even less efficient.

Is there a better way to do this query? Is there perhaps a window function I am missing in Postgres that would handle it?

2

Use the power of UNION ALL:

WITH init AS (
   SELECT created_at
   FROM posts 
   WHERE id = 5869
)
(
   (SELECT posts.*
    FROM posts
       CROSS JOIN init
    WHERE posts.created_at <= init.created_at 
    ORDER BY posts.created_at DESC 
    LIMIT 11) 
 UNION ALL 
   (SELECT posts.* 
    FROM posts 
       CROSS JOIN init 
    WHERE posts.created_at > init.created_at 
    ORDER BY posts.created_at 
    LIMIT 10)
);

This query assumes that there are no duplicates in created_at.

For good performance, you need indexes on id (you have that with the primary key) and created_at.

If you need the result sorted, use my query as a subselect and add an ORDER BY.

  • This works (after updating the timestamp precision to better avoid potential conflicts). Gets the query time down to 100-150ms, which is more acceptable. On a whim, I took your 3 child queries and ran them separately and it shaved a further 20-40ms off, getting it to sub 80ms on my personal machine. I'm going to try increasing the dataset further, but I think this is the route I will take. Thanks! – Josh Janusch Nov 1 at 15:23
2
WITH 
cte1 AS ( SELECT created_at, 10 N 
          FROM posts WHERE id=5869 ), /* parameters */
cte2 AS ( SELECT posts.created_at,
                 LAG(posts.created_at, cte1.N, CAST('2000-01-01' AS DATE)) OVER () lbound, 
                 LEAD(posts.created_at, cte1.N, CAST('2000-01-01' AS DATE)) OVER () ubound
          FROM posts, cte1 )
SELECT posts.* 
FROM posts, cte1, cte2
WHERE posts.created_at BETWEEN cte2.lbound AND cte2.ubound
  AND cte2.created_at = cte1.created_at
ORDER BY posts.id

sample fiddle (N set to 3, id set to 10).

PS. I understand that window in LAG/LEAD must be specified like ORDER BY posts.created_at ASC ROWS BETWEEN [UNBOUNDED | cte1.N] PRECEIDING AND [UNBOUNDED | cte1.N] FOLLOWING. May be added if needed really.

  • This works (albeit after I used ORDER BY in the lag/lead), but still has performance issues. After indexing the created_at column on the previous dataset, it still takes 400-500ms. An improvement over the 800ms, but not nearly as much of one as the other answer so far. Thanks for the reply, though, it actually opened my eyes to new ways to use CTEs in the future. – Josh Janusch Nov 1 at 15:18
  • @JoshJanusch Postgres unconditionally matherialized CTEs. If combine everything into one complex query its performance must insease additionally. – Akina Nov 1 at 16:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.