CODEWITHSUNDEEP
sqlpostgresql
Chris
Chris

Reputation: 5073

Optimizing this SQL query by reducing the inner queries?

I have the following postgres query that gives me the results I want but I feel like it isn't optimized as it's running the same inner query twice. I've tried a group by but it keeps saying i have to add xdata.column_name to the group by. I have to get the total_count because I'm creating pagination with these results. Any help would be appreciated.

SELECT total.total_count, xdata.* FROM (SELECT * FROM (SELECT *, CAST(celery_taskresult.meta -> 'args' ->> 0 AS INT) AS user_id, celery_taskresult.meta ->> 'name' AS task_name FROM celery_taskresult) AS x 
    WHERE x.user_id IS NOT NULL) as xdata
LEFT JOIN (SELECT COUNT(z.id) AS total_count, 0 AS b FROM (SELECT *, CAST(celery_taskresult.meta -> 'args' ->> 0 AS INT) AS user_id, celery_taskresult.meta ->> 'name' AS task_name FROM celery_taskresult) AS z 
    WHERE z.user_id IS NOT NULL) as total ON 0 = total.b
ORDER BY xdata.id ASC
LIMIT 25 OFFSET 0

Upvotes: 0

Views: 47

Answers (2)

derek.wolfe
derek.wolfe

Reputation: 1116

I have not used postgresql in a while but selecting COUNT(*) OVER() should work

  SELECT COUNT(*) OVER() AS total_count, 
         xdata.* 
    FROM (SELECT * 
            FROM (SELECT *, 
                         CAST(celery_taskresult.meta -> 'args' ->> 0 AS INT) AS user_id, 
                         celery_taskresult.meta ->> 'name' AS task_name 
                    FROM celery_taskresult) AS X 
           WHERE x.user_id IS NOT NULL) AS xdata
ORDER BY xdata.id ASC
LIMIT 25 OFFSET 0

EDIT: Docs on PostgreSQL window functions - Window Functions

Upvotes: 1

fatih kosal
fatih kosal

Reputation: 185

You can use cte. https://www.tutorialspoint.com/postgresql/postgresql_with_clause.htm. For example
With cte as (select * from a) select * from cte;
Replace select * from a with your inner select and use it.

Upvotes: 0

Related Questions