Sample Header Ad - 728x90

Optimal way to get a total count of rows in a paged query in Postgres?

2 votes
1 answer
5911 views
I need to improve the performance of a paged query for customer orders in a Type2 Postgres db (always insert a new record with a new ID, the newest ID is the current version of the record). Changing away from Type2 is not an option at this time. The query I have is two queries with the same CTE in both:
WITH customer_orders AS (
    select id, order_id, customer_id,
    "name", country, state, county, source_system, 
    is_deleted, created_at, updated_at, deleted_at,
    created_by, updated_by, deleted_by, 
    rank() over (partition by order_id order by id desc) as entity_rank 
    from orders WHERE customer_id = $1 and is_deleted= $2
  )
SELECT * FROM customer_orders where entity_rank = 1 ORDER BY id DESC LIMIT $3 OFFSET $4;


WITH customer_orders AS (
    select id, order_id, customer_id,
    "name", country, state, county, source_system, 
    is_deleted, created_at, updated_at, deleted_at,
    created_by, updated_by, deleted_by, 
    rank() over (partition by order_id order by id desc) as entity_rank 
    from orders WHERE customer_id = $1 and is_deleted= $2
    )
SELECT count(id) FROM customer_orders where entity_rank = 1;
But I wonder if there's a better way to do this, can I select from the CTE twice, once for the paging (limit + offset) and once for the total number of records? I'll be running this as two separate queries from a Node process. It seems like it should be doable in one query but I can't get it. Indexes: id (PK), order_id, customer_id, is_deleted (1 on each of those)
Asked by jcollum (229 rep)
Aug 18, 2023, 11:26 PM
Last activity: Aug 29, 2023, 08:07 PM