CODEWITHSUNDEEP
sqlpostgresql
Thomas
Thomas

Reputation: 88707

Efficiently order by columns from different tables

We're currently trying to improve a system that allows the user to filter and sort a large list of objects (> 100k) by fields that are not being displayed. Since the fields can be selected dynamically we'd plan to build the query dynamically as well.

That doesn't sound too hard and the basics are done easily but the problem lies in how the data is structured. In some cases some more or less expensive joins would be needed which could some up to a quite expensive query, especially when those joins are combined (i.e. select * from table join some_expensive_join join another_expensive_join ...).

Filtering wouldn't be that big a problem since we can use intersections.

Ordering, however, would require us to first build a table that contains all necessary data which if being done via a huge select statement with all those joins would become quite expensive.

So the question is: is there a more efficient way to do that?

I could think of doing it like this:

Does that make sense? If yes, how could this be done in Postgresql 9.4 (we currently can't upgrade so 9.5+ solutions though welcome wouldn't help as right now).

Upvotes: 2

Views: 93

Answers (1)

wildplasser
wildplasser

Reputation: 44250

Does this help, or is it too trivial? (the subqueries could be prefab join views)

SELECT t0.id, t0.a,t0.b,t0.c, ...
FROM main_table t0
JOIN ( SELECT t1.id AS id
        , rank() OVER (ORDER BY whatever) AS rnk
        FROM different_tables_or_JOINS
        ) AS t1 ON t1.id=t0.id

JOIN ( SELECT t2.id AS id
        , rank() OVER (ORDER BY whatever) AS rnk
        FROM different_tables_or_JOINS2
        ) AS t2 ON t2.id=t0.id

        ...

ORDER BY t1.rnk
        , t2.rnk
        ...
        , t0.id
        ;

Upvotes: 1

Related Questions