0

I followed this post, and more precisely this answer, to be able to obtain the row count from my PostgreSQL query before LIMIT and OFFSET are applied.

Therefore using this kind of code:

SELECT foo
      ,count(*) OVER() AS filtered_count
FROM   bar
WHERE  <some condition>
ORDER  BY <some col>
LIMIT  <pagesize>
OFFSET <offset>

I obtain the count of every row fitting the WHERE clause.

That's pretty cool, and I wonder if there is a similar way to also have the full count of rows, before the WHERE is made?

Thanks in advance for your help!

Community
  • 1
  • 1
pidupuis
  • 343
  • 6
  • 15
  • You cant because with or without the WHERE, are 2 different query. With different plans and different reading. So, as far as I know, there is no way to do this. – user_0 Dec 10 '14 at 16:00

1 Answers1

1

I would say no. Not in the way you want.

What you are suggesting is basically having 2 different WHERES in the same query and that does not make sense.

I would first get all the data and apply the WHERE after like. In this way you have a single query and you apply the WHERE after the count(*):

-- Option 1
SELECT *
FROM
(
    SELECT *
          ,count(*) OVER() AS filtered_count
    FROM bar
) baz
WHERE  <some condition>

EDIT:

If you only want a count(*) of the full bar table, performance might be better doing:

-- Option 2
SELECT *, (SELECT count(*) FROM bar)
FROM bar
WHERE  <some condition>

bar = table w/ 500000 rows, cost of 436 (ANALYZE command):

Option 1: 1541 Option 2: 1326

bar = complex query, cost of 7477:

Option 1: 7691 Option 2: 15005

I would go always for Option 1 for 2 reasons. First the speedup we get in the 1st case is neglectable comparing to the cost of the whole operation. And second, because whatever bar is, with Option 1 you only have to specify it one time

Filipe Roxo
  • 622
  • 3
  • 10
  • 1
    Thanks! In term of performance, what's better between this single embedded query and the use of two different queries? – pidupuis Dec 11 '14 at 14:08
  • 1
    With 2 queries, no matter where you do the 2nd query, in FROM or SELECT clause, you'll have to do an access to bar twice. If that cost is cheaper than the overhead of the OVER() it's better 2 have 2 queries. Check my EDIT – Filipe Roxo Dec 11 '14 at 19:00
  • Thanks for your response but sorry I don't understand which query corresponds to which option in your post :s – pidupuis Dec 12 '14 at 08:41
  • 1 is the 1st, 2 is the 2nd, added comments to be clear – Filipe Roxo Dec 12 '14 at 12:10