How to optimize a simple join

I have table "assets":

CREATE TABLE assets (
  id TEXT PRIMARY KEY,
  title TEXT
)

CREATE TABLE assets (
  id TEXT PRIMARY KEY,
  title TEXT
)

I have table "assetmeta":

CREATE TABLE assetmeta (
  id TEXT PRIMARY KEY,
  accessstatus TEXT
)

CREATE TABLE assetmeta (
  id TEXT PRIMARY KEY,
  accessstatus TEXT
)

I have a view "getpublicassets":

CREATE VIEW getpublicassets AS
SELECT * FROM assets LEFT JOIN assetsmeta ON assetsmeta.id = assets.id
WHERE accessstatus = 'public'

CREATE VIEW getpublicassets AS
SELECT * FROM assets LEFT JOIN assetsmeta ON assetsmeta.id = assets.id
WHERE accessstatus = 'public'

Then I run it:

SELECT * FROM getpublicassets LIMIT 100

SELECT * FROM getpublicassets LIMIT 100

When I EXPLAIN ANALYZE it says it is performing a loop for each row and it's slow. What can I do to optimize my view?
Tried adding an index:

CREATE INDEX asset_public ON assetmeta (
    accessstatus ASC
 );

CREATE INDEX asset_public ON assetmeta (
    accessstatus ASC
 );

Doesnt seem to matter

Supabase•4y ago•

11 replies