I have this base query and execution time around 400ms:
SELECT
s.parcelno
FROM export.scans s
INNER JOIN plnum_customerno cp ON cp.pl_number = s.parcelno
WHERE
s.type_of_scan = '05'
AND s.sdate::date BETWEEN '2017-10-01' AND '2017-10-31'
AND cp.customerno = '17500000006057710'
GROUP BY s.parcelno
Explain:
HashAggregate (cost=46289.64..46289.65 rows=1 width=16) (actual time=369.385..369.404 rows=195 loops=1)
Output: s.parcelno
Group Key: s.parcelno
-> Hash Join (cost=12144.02..46289.64 rows=1 width=16) (actual time=155.755..369.327 rows=195 loops=1)
Output: s.parcelno
Hash Cond: ((cp.pl_number)::bpchar = s.parcelno)
-> Foreign Scan on public.plnum_customerno cp (cost=371.57..34420.40 rows=9678 width=15) (actual time=9.976..267.470 rows=51054 loops=1)
Output: cp.pl_number
Remote SQL: SELECT pl_number FROM public.plnum_customerno WHERE ((customerno = '17500000006057710'::text))
-> Hash (cost=11768.05..11768.05 rows=352 width=16) (actual time=45.691..45.691 rows=66688 loops=1)
Output: s.parcelno
Buckets: 1024 Batches: 1 Memory Usage: 2280kB
-> Append (cost=0.00..11768.05 rows=352 width=16) (actual time=4.953..36.296 rows=66688 loops=1)
-> Seq Scan on export.scans s (cost=0.00..0.00 rows=1 width=60) (actual time=0.000..0.000 rows=0 loops=1)
Output: s.parcelno
Filter: ((s.type_of_scan = '05'::bpchar) AND ((s.sdate)::date >= '2017-10-01'::date) AND ((s.sdate)::date <= '2017-10-31'::date))
-> Index Scan using scans_2017_01_type_of_scan on export.scans_2017_01 s_1 (cost=0.14..8.17 rows=1 width=60) (actual time=0.002..0.002 rows=0 loops=1)
Output: s_1.parcelno
Index Cond: (s_1.type_of_scan = '05'::bpchar)
Filter: (((s_1.sdate)::date >= '2017-10-01'::date) AND ((s_1.sdate)::date <= '2017-10-31'::date))
-> Index Scan using scans_2017_02_type_of_scan on export.scans_2017_02 s_2 (cost=0.14..8.17 rows=1 width=60) (actual time=0.001..0.001 rows=0 loops=1)
Output: s_2.parcelno
Index Cond: (s_2.type_of_scan = '05'::bpchar)
Filter: (((s_2.sdate)::date >= '2017-10-01'::date) AND ((s_2.sdate)::date <= '2017-10-31'::date))
-> Index Scan using scans_2017_03_type_of_scan on export.scans_2017_03 s_3 (cost=0.14..8.17 rows=1 width=60) (actual time=0.000..0.000 rows=0 loops=1)
Output: s_3.parcelno
Index Cond: (s_3.type_of_scan = '05'::bpchar)
Filter: (((s_3.sdate)::date >= '2017-10-01'::date) AND ((s_3.sdate)::date <= '2017-10-31'::date))
-> Index Scan using scans_2017_04_type_of_scan on export.scans_2017_04 s_4 (cost=0.14..8.17 rows=1 width=60) (actual time=0.001..0.001 rows=0 loops=1)
Output: s_4.parcelno
Index Cond: (s_4.type_of_scan = '05'::bpchar)
Filter: (((s_4.sdate)::date >= '2017-10-01'::date) AND ((s_4.sdate)::date <= '2017-10-31'::date))
-> Index Scan using scans_2017_05_type_of_scan on export.scans_2017_05 s_5 (cost=0.14..8.17 rows=1 width=60) (actual time=0.000..0.000 rows=0 loops=1)
Output: s_5.parcelno
Index Cond: (s_5.type_of_scan = '05'::bpchar)
Filter: (((s_5.sdate)::date >= '2017-10-01'::date) AND ((s_5.sdate)::date <= '2017-10-31'::date))
-> Index Scan using scans_2017_06_type_of_scan on export.scans_2017_06 s_6 (cost=0.14..8.17 rows=1 width=60) (actual time=0.000..0.000 rows=0 loops=1)
Output: s_6.parcelno
Index Cond: (s_6.type_of_scan = '05'::bpchar)
Filter: (((s_6.sdate)::date >= '2017-10-01'::date) AND ((s_6.sdate)::date <= '2017-10-31'::date))
-> Index Scan using scans_2017_07_type_of_scan on export.scans_2017_07 s_7 (cost=0.14..8.17 rows=1 width=60) (actual time=0.000..0.000 rows=0 loops=1)
Output: s_7.parcelno
Index Cond: (s_7.type_of_scan = '05'::bpchar)
Filter: (((s_7.sdate)::date >= '2017-10-01'::date) AND ((s_7.sdate)::date <= '2017-10-31'::date))
-> Bitmap Heap Scan on export.scans_2017_08 s_8 (cost=7.32..407.65 rows=2 width=15) (actual time=0.168..0.168 rows=0 loops=1)
Output: s_8.parcelno
Recheck Cond: (s_8.type_of_scan = '05'::bpchar)
Filter: (((s_8.sdate)::date >= '2017-10-01'::date) AND ((s_8.sdate)::date <= '2017-10-31'::date))
Rows Removed by Filter: 403
Heap Blocks: exact=36
-> Bitmap Index Scan on scans_2017_08_type_of_scan (cost=0.00..7.32 rows=404 width=0) (actual time=0.037..0.037 rows=403 loops=1)
Index Cond: (s_8.type_of_scan = '05'::bpchar)
-> Seq Scan on export.scans_2017_09 s_9 (cost=0.00..3.21 rows=1 width=15) (actual time=0.008..0.008 rows=0 loops=1)
Output: s_9.parcelno
Filter: ((s_9.type_of_scan = '05'::bpchar) AND ((s_9.sdate)::date >= '2017-10-01'::date) AND ((s_9.sdate)::date <= '2017-10-31'::date))
Rows Removed by Filter: 55
-> Bitmap Heap Scan on export.scans_2017_10 s_10 (cost=1175.63..11300.01 rows=341 width=15) (actual time=4.771..32.496 rows=66688 loops=1)
Output: s_10.parcelno
Recheck Cond: (s_10.type_of_scan = '05'::bpchar)
Filter: (((s_10.sdate)::date >= '2017-10-01'::date) AND ((s_10.sdate)::date <= '2017-10-31'::date))
Heap Blocks: exact=4797
-> Bitmap Index Scan on scans_2017_10_type_of_scan (cost=0.00..1175.55 rows=68150 width=0) (actual time=4.221..4.221 rows=66688 loops=1)
Index Cond: (s_10.type_of_scan = '05'::bpchar)
Planning time: 2.633 ms
Execution time: 369.879 ms
Problem occurs when i add another LEFT JOIN with foreign table, execution is extended to 1.5min:
SELECT
s.parcelno,
p.sendparcelref1
FROM export.scans s
INNER JOIN plnum_customerno cp ON cp.pl_number = s.parcelno
LEFT JOIN geo_parcel p ON p.parcelnumber = s.parcelno
WHERE
s.type_of_scan = '05'
AND s.sdate::date BETWEEN '2017-10-01' AND '2017-10-31'
AND cp.customerno = '17500000006057710'
GROUP BY s.parcelno, p.sendparcelref1
Explain:
HashAggregate (cost=139309.92..139309.93 rows=1 width=28) (actual time=89065.944..89065.963 rows=195 loops=1)
Output: s.parcelno, p.sendparcelref1
Group Key: s.parcelno, p.sendparcelref1
-> Nested Loop Left Join (cost=12244.03..139309.91 rows=1 width=28) (actual time=777.724..89065.616 rows=195 loops=1)
Output: s.parcelno, p.sendparcelref1
-> Hash Join (cost=12144.02..46289.64 rows=1 width=16) (actual time=161.713..386.227 rows=195 loops=1)
Output: s.parcelno
Hash Cond: ((cp.pl_number)::bpchar = s.parcelno)
-> Foreign Scan on public.plnum_customerno cp (cost=371.57..34420.40 rows=9678 width=15) (actual time=12.592..270.692 rows=51054 loops=1)
Output: cp.pl_number
Remote SQL: SELECT pl_number FROM public.plnum_customerno WHERE ((customerno = '17500000006057710'::text))
-> Hash (cost=11768.05..11768.05 rows=352 width=16) (actual time=51.723..51.723 rows=66688 loops=1)
Output: s.parcelno
Buckets: 1024 Batches: 1 Memory Usage: 2280kB
-> Append (cost=0.00..11768.05 rows=352 width=16) (actual time=8.611..41.957 rows=66688 loops=1)
-> Seq Scan on export.scans s (cost=0.00..0.00 rows=1 width=60) (actual time=0.001..0.001 rows=0 loops=1)
Output: s.parcelno
Filter: ((s.type_of_scan = '05'::bpchar) AND ((s.sdate)::date >= '2017-10-01'::date) AND ((s.sdate)::date <= '2017-10-31'::date))
-> Index Scan using scans_2017_01_type_of_scan on export.scans_2017_01 s_1 (cost=0.14..8.17 rows=1 width=60) (actual time=0.003..0.003 rows=0 loops=1)
Output: s_1.parcelno
Index Cond: (s_1.type_of_scan = '05'::bpchar)
Filter: (((s_1.sdate)::date >= '2017-10-01'::date) AND ((s_1.sdate)::date <= '2017-10-31'::date))
-> Index Scan using scans_2017_02_type_of_scan on export.scans_2017_02 s_2 (cost=0.14..8.17 rows=1 width=60) (actual time=0.000..0.000 rows=0 loops=1)
Output: s_2.parcelno
Index Cond: (s_2.type_of_scan = '05'::bpchar)
Filter: (((s_2.sdate)::date >= '2017-10-01'::date) AND ((s_2.sdate)::date <= '2017-10-31'::date))
-> Index Scan using scans_2017_03_type_of_scan on export.scans_2017_03 s_3 (cost=0.14..8.17 rows=1 width=60) (actual time=0.000..0.000 rows=0 loops=1)
Output: s_3.parcelno
Index Cond: (s_3.type_of_scan = '05'::bpchar)
Filter: (((s_3.sdate)::date >= '2017-10-01'::date) AND ((s_3.sdate)::date <= '2017-10-31'::date))
-> Index Scan using scans_2017_04_type_of_scan on export.scans_2017_04 s_4 (cost=0.14..8.17 rows=1 width=60) (actual time=0.001..0.001 rows=0 loops=1)
Output: s_4.parcelno
Index Cond: (s_4.type_of_scan = '05'::bpchar)
Filter: (((s_4.sdate)::date >= '2017-10-01'::date) AND ((s_4.sdate)::date <= '2017-10-31'::date))
-> Index Scan using scans_2017_05_type_of_scan on export.scans_2017_05 s_5 (cost=0.14..8.17 rows=1 width=60) (actual time=0.001..0.001 rows=0 loops=1)
Output: s_5.parcelno
Index Cond: (s_5.type_of_scan = '05'::bpchar)
Filter: (((s_5.sdate)::date >= '2017-10-01'::date) AND ((s_5.sdate)::date <= '2017-10-31'::date))
-> Index Scan using scans_2017_06_type_of_scan on export.scans_2017_06 s_6 (cost=0.14..8.17 rows=1 width=60) (actual time=0.001..0.001 rows=0 loops=1)
Output: s_6.parcelno
Index Cond: (s_6.type_of_scan = '05'::bpchar)
Filter: (((s_6.sdate)::date >= '2017-10-01'::date) AND ((s_6.sdate)::date <= '2017-10-31'::date))
-> Index Scan using scans_2017_07_type_of_scan on export.scans_2017_07 s_7 (cost=0.14..8.17 rows=1 width=60) (actual time=0.000..0.000 rows=0 loops=1)
Output: s_7.parcelno
Index Cond: (s_7.type_of_scan = '05'::bpchar)
Filter: (((s_7.sdate)::date >= '2017-10-01'::date) AND ((s_7.sdate)::date <= '2017-10-31'::date))
-> Bitmap Heap Scan on export.scans_2017_08 s_8 (cost=7.32..407.65 rows=2 width=15) (actual time=0.252..0.252 rows=0 loops=1)
Output: s_8.parcelno
Recheck Cond: (s_8.type_of_scan = '05'::bpchar)
Filter: (((s_8.sdate)::date >= '2017-10-01'::date) AND ((s_8.sdate)::date <= '2017-10-31'::date))
Rows Removed by Filter: 403
Heap Blocks: exact=36
-> Bitmap Index Scan on scans_2017_08_type_of_scan (cost=0.00..7.32 rows=404 width=0) (actual time=0.057..0.057 rows=403 loops=1)
Index Cond: (s_8.type_of_scan = '05'::bpchar)
-> Seq Scan on export.scans_2017_09 s_9 (cost=0.00..3.21 rows=1 width=15) (actual time=0.013..0.013 rows=0 loops=1)
Output: s_9.parcelno
Filter: ((s_9.type_of_scan = '05'::bpchar) AND ((s_9.sdate)::date >= '2017-10-01'::date) AND ((s_9.sdate)::date <= '2017-10-31'::date))
Rows Removed by Filter: 55
-> Bitmap Heap Scan on export.scans_2017_10 s_10 (cost=1175.63..11300.01 rows=341 width=15) (actual time=8.336..37.802 rows=66688 loops=1)
Output: s_10.parcelno
Recheck Cond: (s_10.type_of_scan = '05'::bpchar)
Filter: (((s_10.sdate)::date >= '2017-10-01'::date) AND ((s_10.sdate)::date <= '2017-10-31'::date))
Heap Blocks: exact=4797
-> Bitmap Index Scan on scans_2017_10_type_of_scan (cost=0.00..1175.55 rows=68150 width=0) (actual time=7.324..7.324 rows=66688 loops=1)
Index Cond: (s_10.type_of_scan = '05'::bpchar)
-> Foreign Scan on public.geo_parcel p (cost=100.01..93020.26 rows=1 width=27) (actual time=454.568..454.568 rows=1 loops=195)
Output: p.sendparcelref1, p.parcelnumber
Remote SQL: SELECT parcelnumber, sendparcelref1 FROM geodata.parcel WHERE ((parcelnumber = $1::character(14)))
Planning time: 4.847 ms
Execution time: 89066.619 ms
Foreign table:
CREATE FOREIGN TABLE geo_parcel(
id bigint NULL,
parcelnumber character varying(14) NULL COLLATE pg_catalog."default",
sendparcelref1 character varying(35) NULL COLLATE pg_catalog."default",
shipment_id bigint NULL
)
SERVER dpd_register
OPTIONS (schema_name 'geodata', table_name 'parcel', use_remote_estimate 'true');
Original table on foreign server:
CREATE TABLE geodata.parcel
(
id serial NOT NULL,
parcelnumber character varying(14) NOT NULL,
parcelnumbercckey character varying(1),
parcelrank character varying(3),
sendparcelref1 character varying(35),
sendparcelref2 character varying(35),
sendparcelref3 character varying(35),
sendparcelref4 character varying(35),
recparcelref character varying(35),
servicecode character varying(3),
ppartnerref1 character varying(35),
ppartnerref2 character varying(35),
ppartnerref3 character varying(35),
ppartnerref4 character varying(35),
dimension integer,
declaredweight double precision,
measuredweight numeric(8,0),
hinsamount double precision,
hinscurrency character varying(3),
hinscontent character varying(35),
hazlq character varying,
hzdpackcode character varying(3),
opcode character varying(3),
pcontent character varying(200),
originparcelnumber character varying(32),
pownerbu character varying(3),
ppartnercode character varying(35),
ascode character varying(6),
bagno character varying(16),
shipment_id bigint,
id_check_receiver_name character varying(35),
id_check_id_num character varying(5),
custom_product_id character varying(20),
cop_amount numeric,
cor_amount numeric,
hinsure integer DEFAULT 0,
CONSTRAINT parcel_pkey PRIMARY KEY (id)
)
WITH (
OIDS=TRUE
);
CREATE INDEX parcel_id_idx
ON geodata.parcel
USING btree
(id);
CREATE INDEX parcel_parcelnumber_idx
ON geodata.parcel
USING btree
(parcelnumber COLLATE pg_catalog."default");
CREATE INDEX parcel_shipment_id_idx
ON geodata.parcel
USING btree
(shipment_id);
My local table is partitioned, main table:
CREATE TABLE export.scans
(
id serial NOT NULL,
parcelno character(14),
type_of_scan character(2),
depot character(4),
sdate timestamp without time zone NOT NULL,
route character(4),
tour character(3),
pcode character(2),
service character(3),
country character(3),
post character(7),
adc1 character(3),
adc2 character(3),
adc3 character(3),
weight character varying,
cust_ref character(50),
pod_ref character(7),
rname character varying,
info character(200),
loc character(35),
CONSTRAINT scans_pri_key PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
Partition example:
CREATE TABLE export.scans_2017_10
(
CONSTRAINT scans_2017_10_sdate_check
CHECK (sdate >= '2017-10-01'::date AND sdate < '2017-11-01'::date)
)
INHERITS (export.scans)
WITH (
OIDS=FALSE
);
CREATE INDEX scans_2017_10_parcelno
ON export.scans_2017_10
USING btree
(parcelno COLLATE pg_catalog."default");
CREATE INDEX scans_2017_10_sdate
ON export.scans_2017_10
USING btree
(sdate);
CREATE INDEX scans_2017_10_type_of_scan
ON export.scans_2017_10
USING btree
(type_of_scan COLLATE pg_catalog."default");
I have tried to put foreign table in CTE but that only reduced the time to 20sec.
Any ideas or solutions are appreciated.
Best Answer
Use pev to see how slow.
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
It's it network latency ?