Byrne<donal> Cap The Count (in Postgres)



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