0

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)

6
  • 1
    Knowing the table and index definitions would be helpful.
    – J.D.
    Aug 18, 2023 at 23:30
  • added the indexes, the select in the CTE is all the fields in the table
    – jcollum
    Aug 18, 2023 at 23:40
  • 2
    Please consider reading this advice
    – mustaccio
    Aug 19, 2023 at 0:44
  • "Indexes: id (PK), order_id, customer_id, is_deleted" - Is this 1, 2, 3, or 4 indexes? Please try to be clearer on the details you provide. Please see mustaccio's advice.
    – J.D.
    Aug 19, 2023 at 3:03
  • 1
    What is a Type2 Postgres db? As opposed to Type1? Or Type-whatever - there are PostgreSQL databases (current supported versions 11 - 15) - those are the only Types of PostgreSQL database that I know about - maybe you could expand on this?
    – Vérace
    Aug 19, 2023 at 15:46

1 Answer 1

3

Yes, you can reuse the same CTE multiple times, and that's typically faster than running multiple queries. (But you can only return from a query once.)

But you don't even have to for the use case. Just add a window function in the outer SELECT:

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
        , row_number() OVER (PARTITION BY order_id ORDER BY id DESC ROWS UNBOUNDED PRECEDING) AS entity_rank
   FROM   orders
   WHERE  customer_id = $1
   AND    is_deleted = $2
   )
SELECT *, count(*) OVER () AS total_count
FROM   customer_orders
WHERE  entity_rank = 1
ORDER  BY id DESC
LIMIT  $3
OFFSET $4;

See:

You don't even need a CTE at all now. A plain subquery does the job. (But doesn't make much of a difference any more since Postgres 12.)

SELECT *, count(*) OVER () AS total_count
FROM  (
   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
        , row_number() OVER (PARTITION BY order_id ORDER BY id DESC ROWS UNBOUNDED PRECEDING) AS entity_rank
   FROM   orders
   WHERE  customer_id = $1
   AND    is_deleted = $2
   ) customer_orders
WHERE  entity_rank = 1
ORDER  BY id DESC
LIMIT  $3
OFFSET $4;

While being at it, I replaced rank() with the more appropriate (and faster) row_number(). I also added the custom frame definition ROWS UNBOUNDED PRECEDING to help performance. (Obsolete, but harmless, in Postgres 16 or later.) See:

This multicolumn index can probably help performance (a lot):

CREATE INDEX foo_idx ON orders (customer_id, is_deleted, order_id, id DESC);

More performance optimization may be possible, highly likely even. But that depends on undisclosed details of your setup. You'd have to provide information for performance questions as requested in the comments.
Basics:

4
  • Dang this is very helpful, will implement this when I'm back at the office
    – jcollum
    Aug 20, 2023 at 20:54
  • 1
    Huge improvement; used the 2nd one. A query that was taking ~25s down to ~200ms; thanks for the help
    – jcollum
    Aug 21, 2023 at 16:27
  • 1
    @jcollum: Nice. Pretty sure this can be much faster still. Test the index I added. Or provide information as instructed. Aug 22, 2023 at 3:29
  • Usually, the best think is not to calculate the total result set count. Estimate with EXPLAIN or only calculate the count on explicit demand. Aug 28, 2023 at 7:12

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .