CODEWITHSUNDEEP
sqlpostgresqlamazon-redshift
AndySavage
AndySavage

Reputation: 1769

Combining a LIMITed SELECT and a total COUNT together?

I have a table called profile containing user information. I need to do a filtered query on this table and get:

  1. The count of rows that matched this query.
  2. The data for the top 5000 matching rows only.

I am looking for an optimal way to do this. Obviously there will need to be at least one scan to do the count, but ideally the DB could be fetching the top matching whilst it does the count.

The following query gives me the correct result, but it looks a bit hacky. I'm wondering if it can be done better?

WITH total AS (
     SELECT COUNT(*) AS total FROM profile 
         WHERE project_id = 1 and some_prop = 100)
SELECT total.total, full_name, other_prop 
    FROM profile
    INNER JOIN total ON 1 = 1
    WHERE project_id = 1 and some_prop = 100
    ORDER BY full_name ASC
    LIMIT 5000

Is there a more efficient way to do this?

Upvotes: 2

Views: 160

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125374

You are scanning the same table twice to apply the filter. With the below you scan the table only once applying the filter and the do the total and list both on the filtered table.

with s as (
    select *
    from profile 
    where project_id = 1 and some_prop = 100
), t as (
    select count(*) as total from s
)
select total, full_name, other_prop 
from s cross join t
order by full_name asc
limit 5000

Window function version

select
    count(*) over() as total,
    full_name,
    other_prop 
from profile
where project_id = 1 and some_prop = 100
order by full_name asc
limit 5000

Upvotes: 2

Related Questions