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 ?
EDIT
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"