1

Suppose I have two tables linked by a foreign-key

example DDL's

CREATE TABLE raw_collection (
    info_datetime DATETIME NOT NULL, 
    asset_id INTEGER NOT NULL, 
    price FLOAT, 
    PRIMARY KEY (info_datetime, asset_id), 
    FOREIGN KEY(asset_id) REFERENCES assets (id)
);

CREATE TABLE assets (
    id INTEGER NOT NULL, 
    asset_symbol VARCHAR, 
    PRIMARY KEY (id)
);

when filtering by asset_symbol on the raw_collection tables, what (and why) would be a faster solution. using a join or creating an in memory table from the asset_symbol table and filtering raw_collection according to it?


the queries

-- query 1
with needed_assets as (
  select id 
  from assets
  where asset_symbol in ('a', 'b'))
select *
from raw_collection
where asset_id in (select id from needed_assets) ;
-- where asset_id in (table needed_assets) ;  -- Postgres only syntax
-- where asset_id in needed_assets ;          -- SQLite only syntax

-- query 2
select r.*
from raw_collection r join assets a on r.asset_id = a.id 
where a.asset_symbol in  ('a', 'b') ;

I am either using PostgreSQL 11 or SQLite 3.28.0

  • AFAIK in PostgreSQL 11 and earlier CTEs are always materialized... and CTE execution is planned independently excluding possible cross-optimizations. – Akina Sep 19 at 13:23
  • Also edited the 2nd query (from * to r.*) as it was selecting from both tables, making the two queries not equivalent. – ypercubeᵀᴹ Sep 19 at 13:34
1

It depends on a number of things. You list postgres in one of your tags, so it should be noted that the CTE will be materialised in full as CTEs are an optimisation fence that block predicate push-down. I'm not sure about sqlite, the other DB you list in the tags, but this is not the case for all databases, SQL Server for one can optimise across CTEs.

So for postgres currently, all other things being equal and appropriate indexes being present, I would expect query 2 to be faster if there is enough data in assets matching asset_symbol in ('a', 'b')) for the difference to be noticeable.

In either case what indexes you have defined will also make a difference. Two things to note in particular are that marking raw_collection.asset_id as a foreign key does not automatically create an index for that column which might limit the query planner's choices, and if you don't have one already then an index covering assets.asset_symbol is likely to be useful unless that table has very few rows.

Note: this is due to change in version 12, which is slated for release soon, though as you state you are using v11 this is not relevant to you yet

0

In PostgreSQL the second query should never be slower.

If indexes can be used to speed up execution depends on the amount of data in each table and the selectivity of the column.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.