2

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?

AndySavage
  • 1,729
  • 1
  • 20
  • 34
  • I don't think there's anything particularly wrong with it given what you're doing, however you can change "inner join total on 1 = 1" to just "cross join total" which makes more sense, although I doubt it will perform any better. – Brian DeMilia Jul 25 '14 at 22:46
  • Basically a duplicate to this: http://stackoverflow.com/questions/156114/best-way-to-get-result-count-before-limit-was-applied-in-php-postgresql/8242764#8242764. Not sure about Redshift though, which is an odd mix of outdated and modern functionality. – Erwin Brandstetter Jul 26 '14 at 03:30
  • Your window method is fastest (so far) on Redshift as well. – AndySavage Jul 26 '14 at 13:30

1 Answers1

2

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
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260