CODEWITHSUNDEEP
postgresqljoinprocessing-efficiency
user3003374
user3003374

Reputation: 73

More Efficient Way to Join Three Tables Together in Postgres

I am attempting to link three tables together in postgres.

All three tables are generated from subqueries. The first table is linked to the second table by the variable call_sign as a FULL JOIN (because I want the superset of entries from both tables). The third table has an INNER JOIN with the second table also on call_sign (but theoretically could have been linked to the first table)

The query runs but is quite slow and I feel will become even slower as I add more data. I realize that there are certain things that I can do to speed things up - like not pulling unnecessary data in the subqueries and not converting text to numbers on the fly. But is there a better way to structure the JOINs between these three tables?

Any advice would be appreciated because I am a novice in postgres.

Here is the code:

select 
(CASE 
    WHEN tmp1.frequency_assigned is NULL 
    THEN tmp2.lower_frequency
    ELSE tmp1.frequency_assigned END) as master_frequency,
(CASE 
    WHEN tmp1.call_sign is NULL 
    THEN tmp2.call_sign
    ELSE tmp1.call_sign END) as master_call_sign,
(CASE 
    WHEN tmp1.entity_type is NULL 
    THEN tmp2.entity_type
    ELSE tmp1.entity_type END) as master_entity_type,
(CASE 
    WHEN tmp1.licensee_id is NULL 
    THEN tmp2.licensee_id
    ELSE tmp1.licensee_id END) as master_licensee_id,
(CASE 
    WHEN tmp1.entity_name is NULL 
    THEN tmp2.entity_name
    ELSE tmp1.entity_name END) as master_entity_name,
tmp3.market_name    
FROM
    (select cast(replace(frequency_assigned, ',','.') as decimal) AS frequency_assigned, 
    frequency_upper_band,
    f.uls_file_number,
    f.call_sign,
    entity_type,
    licensee_id,
    entity_name
    from combo_fr f INNER JOIN combo_en e 
    ON f.call_sign=e.call_sign
    ORDER BY frequency_assigned DESC) tmp1
FULL JOIN
    (select cast(replace(lower_frequency, ',','.') as decimal) AS lower_frequency, 
    upper_frequency,
    e.uls_file_number,
    mf.call_sign,
    entity_type,
    licensee_id,
    entity_name
    FROM market_mf mf INNER JOIN combo_en e
    ON mf.call_sign=e.call_sign
    ORDER BY lower_frequency DESC) tmp2
ON tmp1.call_sign=tmp2.call_sign
INNER JOIN 
    (select en.call_sign,
    mk.market_name
    FROM combo_mk mk
    INNER JOIN combo_en en
    ON mk.call_sign=en.call_sign) tmp3
ON tmp2.call_sign=tmp3.call_sign 
ORDER BY master_frequency DESC;

Upvotes: 2

Views: 6662

Answers (2)

Dario
Dario

Reputation: 2723

After creating indexes on call_sign for all four involved tables, try this:

WITH nodup AS (
  SELECT call_sign FROM market_mf 
  EXCEPT SELECT call_sign FROM combo_fr
) SELECT     
      CAST(REPLACE(u.master_frequency_string, ',','.') AS DECIMAL) 
              AS master_frequency,
      u.call_sign AS master_call_sign,
      u.entity_type AS master_entity_type,
      u.licensee_id AS master_licensee_id,
      u.entity_name AS master_entity_name,
      combo_mk.market_name
    FROM (SELECT frequency_assigned AS master_frequency_string, call_sign, 
                 entity_type, licensee_id, entity_name
            FROM combo_fr
          UNION ALL SELECT lower_frequency, call_sign, 
                 entity_type, licensee_id, entity_name
            FROM market_mf INNER JOIN nodup USING (call_sign)
      ) AS u
    INNER JOIN combo_en USING (call_sign)
    INNER JOIN combo_mk USING (call_sign)
    ORDER BY 1 DESC;

I post this because this is the simplest way to understand what you need.

  • If there are no call_sign values which appear in both market_mf and combo_fr, WITH nodup ... and INNER JOIN nodup ... can be omitted.
  • I am making the assumption that call_sign is unique in both combo_fr and market_mf ( = there are no two records in each table with the same value), even if there can be values which can appear in both tables.
  • It is very unfortunate that you order by a computed column, and that the computation is so silly. A certain optimization would be to convert the frequency strings once and for all in the table itself. The steps would be: (1) add numeric frequncy columns to your tables (2) populate them with the values converted from the current text columns (3) convert new values directly into the new columns, by inputting them with a locale which has the desired decimal separator.

Upvotes: 0

Chris Curvey
Chris Curvey

Reputation: 10389

you'll want to unwind those queries and do it all in one join, if you can. Soemthing like:

select <whatever you need>
from combo_fr f
JOIN combo_en e ON f.call_sign=e.call_sign
JOIN market_mf mf mf ON mf.call_sign=e.call_sign
JOIN combo_mk mk ON mk.call_sign=en.call_sign

I can't completely grok what you're doing, but some of the join clauses might have to become LEFT JOINs in order to deal with places where the call sign does or does not appear.

Upvotes: 2

Related Questions