Cap the count (in postgres)

back

2020-01-15

the problem: i had a query that should return the first 20 rows of a loooooooot of matches.

running without the count, the query took about 100ms. with the count; around a minute. was over 100k rows.

Say that the query looks like:

        
        SELECT *, count(*) OVER()
        FROM users
        WHERE user_name LIKE 'Foo%'
        LIMIT 20
        
    

If you want to stop the count after 1000 results, and show the first 20 results then:

        
        WITH capped AS ( SELECT *
                         FROM users
                         WHERE user_name LIKE 'Foo%'
                         LIMIT 1000 ),
             limited AS ( SELECT * FROM capped LIMIT 20 ),
             count AS ( SELECT count(*) FROM capped )
        SELECT limited.*, count.*
        FROM limited, count