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