CODEWITHSUNDEEP
sqldatabasepostgresqllarge-data
Vivek
Vivek

Reputation: 2755

Optimize an SQL query that has sub queries in select clause and filters based on these sub query results

I have a pgsql query in the following format.

SELECT
    field_1,
    field_2,
    ARRAY (SELECT sub_field_1
            FROM sub_table where condition
    ) as field_3
FROM table
WHERE field_1 LIKE '%xyz%'

My actual query has many more sub queries with complex where conditions.

Initially the database was small and the query ran fine but as database size grew, the query started taking unacceptably long time to run.

I tried adding LIMIT and OFFSET to the query to fetch limited number of results at a time, but it does not seem to have any effect on the query execution time. Why is that so?

Is there any other way to make the query run faster? (I have already done Indexing. Also used JOINS instead of sub query wherever possible)

Is there any other approach to solve this problem? My end goal is to fetch data from a number of large database tables and generate a table in the front-end. A single column in front end table can be multiple rows from a different database table.

Upvotes: 0

Views: 51

Answers (1)

John Stark
John Stark

Reputation: 1297

I can think of two way to approach this that might get you some performance gain. The first would be to use a common table expression (WITH statement) to set up the sub_table, and then join against it:

--* Using a CTE (Common Table Expression) WITH sub_table AS ( SELECT sub_field_1, table_1_key FROM sub_table WHERE condition )

SELECT
  field_1,
  field_2,
  field_3
FROM table t
JOIN sub_table st ON st.table_1_key = t.table_1_key
WHERE field_1 LIKE '%xyz%'

Depending on the type of data you're storing though, and how large the dataset is, it may make better sense to stand up a temporary table to house the data instead of using a common table expression, because you can define keys/indexes on temporary tables. 

--* Using a tempoorary table that leverages a primary key
CREATE TEMPORARY TABLE IF NOT EXISTS temp_sub_table (
  table_1_key BIGINT PRIMARY KEY,
  sub_field_1 VARCHAR(20)
);

INSERT INTO temp_sub_table (table_1_key, sub_field_1)
  SELECT
    table_1_key ('table_1_key'),
    sub_field_1 ('sub_field_1')
  FROM sub_table
  WHERE condition

SELECT
  field_1,
  field_2,
  field_3
FROM table t
JOIN temp_sub_table st ON st.table_1_key = t.table_1_key
WHERE field_1 LIKE '%xyz%'

It's been a little while since I've used this syntax and I'm not on a computer with an IDE at the moment to check; so the code should be close, but let me know if anything looks blatantly wrong and I can debug/edit my answer.

Hope this helps!

Upvotes: 1

Related Questions