I have 2 tables one with around 1M entries and other around 4M. I want to join these tables in a materialized view. But the materialized view creation is taking too long ~4 hours.
I don't see the indexes being used, which is fine as per this answer. When a join involves the whole tables then indices don't help so much.
So what is the workaround? My tables have time-series data. Would TimeScaleDB extension be of any help? Any other suggestion to improve the performance ?
CREATE TABLE public.fco_assets ( id serial, symbol character varying(64) NOT NULL, currency integer NOT NULL, CONSTRAINT fco_assets_pkey1 PRIMARY KEY (id), CONSTRAINT fco_assets_symbol_key1 UNIQUE (symbol) ) CREATE TABLE public.fco_prices ( aid integer NOT NULL, cid integer NOT NULL, "time" timestamp without time zone NOT NULL, close double precision, CONSTRAINT fco_prices_pkey PRIMARY KEY (aid, cid, "time") ) CREATE TABLE public.fco_chr_res ( pid integer NOT NULL, aid integer NOT NULL, cid integer NOT NULL, "time" timestamp without time zone NOT NULL, res double precision NOT NULL, CONSTRAINT fco_chr_res_pkey PRIMARY KEY (pid, aid, cid, "time") )
There are around 8M rows in fco_chr_res. I have created various indices on price and res tables besides the primary key which should act as an index.
select count(*) FROM fco_prices pr join fco_assets a on pr.aid = a.id and pr.cid=a.currency LEFT JOIN fco_chr_res ch ON pr.aid = ch.aid AND pr.cid = ch.cid AND pr."time" = ch."time"
For above query here is the Explain/Analysis report
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
CREATE INDEX fco_assets_idx_id_currency ON "fco_assets" ("id","currency"); CREATE INDEX fco_res_idx_aid_cid_time ON "fco_chr_res" ("aid","cid","time");
SELECT count(*) FROM fco_prices pr JOIN fco_assets a ON pr.aid = a.id AND pr.cid = a.currency LEFT JOIN fco_chr_res ch ON pr.aid = ch.aid AND pr.cid = ch.cid AND pr."time" = ch."time"