CODEWITHSUNDEEP
sqlpostgresqlquery-optimization
Bo.
Bo.

Reputation: 2541

SQL subquery sort

Could one be so kind to assist me with a following:

I have a query that results in two columns one being straight (columnA) from a table while other generated from subquery (columnB). If I do a sort (i.e. ORDER BY) on columnB I get much slower response then when doing the same on columnA (50+ times). Is there a way to speed up the sort on columnB in order to come close to speeds achieved when sorting columnA?

Note: Engine is Postgres

Update: Query looks similar to:

select columnA, array_to_string(array(select ... from tableB where ...), '%') as columnB
from tableA
where ... 
order by columnA

Any advice is much appreciated.

Update #2: Solved it by doing sort in new query and then feeding the results to the main query (using where statement to select only partial number of rows instead of a whole set which gave me performance I needed). Thanks everybody that replied.

Upvotes: 0

Views: 312

Answers (3)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95532

In your query

select columnA, array_to_string(array(select ... from tableB where ...), '%') as columnB
from tableA
where ... 
order by columnA

operations on columnB can't take advantage of an index. Not only that, the sort will have to deal with columns the width of many concatenated rows.

Your best bet is to reconsider why you need this sorted, because the sort order of the expression array_to_string(...) is arbitrary. It's arbitrary, because you say you're not sorting within the SELECT statement that's an argument to array().

I am using array_to_string to capture a number of values that I need to process later. Do you see an alternative?

A SELECT statement will capture any number of values.

If you need "to further process" some values in sorted order, you're probably better off returning the results of a SELECT...ORDER BY statement without using any array functions. That way, your application code can process the values in order just by walking the result set. You won't have to parse values out of a "%" delimited string.

Upvotes: 2

HLGEM
HLGEM

Reputation: 96552

You could put the unsorted data into a temp table and then index column b. Then run a simple select with the order by on the now indexed column. No guarantees this will be faster, but it is something to try.

Upvotes: 2

Jasmine
Jasmine

Reputation: 4029

Since your "ColumnB" is a computed value, there is no index which could be used to speed up the sort. ColumnA probably is already sorted, so it's fast. There is nothing you can do to speed up the sorting of these computed values, except to pre-calculate them and put them in a table. This is a big reason why data warehouses typically don't work against the live data, but export daily roll-ups instead.

Upvotes: 1

Related Questions