How to optimize query that needs to join BigQuery with Postgres?

Queries and Resources
1

Hi,

I have a module that is taking a long time to load (50 seconds) and I think it is because I am taking the results of a Bigquery query and a Postgres query, and joining them using Query JSON with SQL. When I hover over the time, it shows ~50 seconds to run the query under Front End. Any ideas on how to speed this up?

Thanks!

2

@benxu
Are you running these on page load? Maybe for postgres query instead create a View and then execute a query the view....

3

@ScottR sorry, not sure what a view is. This is the only thing i could find in the documentation?List View | Retool Component Library

Also, both the BigQuery and Postgres queries run fine on their own. It is the Query JSON with SQL query that is super slow

4

Sorry, I meant a View in the database.

How much data is being queried? And can you paste the query with JSON here?

5 
WITH
output_address_on_multiple_chains AS (
  SELECT 
      chain,
      address,
      full_name,
      inferred_entity,
      labels,
      volume_1y,
      bp_volume_1y
  FROM
      {{Volume1yLimited.data}}
  WHERE
      chain = ANY({{ multiselect1.selectedLabels }}) AND
      CASE 
          WHEN {{switch1.value===true}} THEN inferred_entity IS NOT NULL 
          ELSE inferred_entity IS NULL OR inferred_entity IS NOT NULL 
      END
),

catalogue AS (
  SELECT 
      address,
      chain_id AS network
  FROM
      {{catalogue.data}}
)

SELECT
    chain,
    address,
    full_name,
    inferred_entity,
    labels,
    volume_1y,
    bp_volume_1y
FROM
    output_address_on_multiple_chains vol
LEFT JOIN
    catalogue
    ON vol.chain = catalogue.network AND vol.address = catalogue.address
WHERE
    catalogue.address IS NULL
ORDER BY
  case when {{ TableVolume1y.sortedColumn == 'volume_1y' }} and {{ TableVolume1y.sortedDesc }} then volume_1y end desc,
  case when {{ TableVolume1y.sortedColumn == 'volume_1y' }} and {{ !TableVolume1y.sortedDesc }} then volume_1y end asc,
  case when {{ TableVolume1y.sortedColumn == 'bp_volume_1y' }} and {{ TableVolume1y.sortedDesc }} then bp_volume_1y end desc,
  case when {{ TableVolume1y.sortedColumn == 'bp_volume_1y' }} and {{ !TableVolume1y.sortedDesc }} then bp_volume_1y end asc,
  volume_1y DESC```

Volume1yLimited is a BigQuery query that returns 1000 rows
catalogue is a Postgres query that returns ~93k rows
6

i think the results from the catalogue are too big and Retool struggles to process it with Query JSON with SQL. going to try and fix up the underlying tables to account for this

7

I agree...
If this is something you don't need to do often, perhaps workflows will help...it's in beta...I believe.
Check out this post... You might be able to do a workflow as suggested by @alina.retool

1 Like