2

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.

  • Did you ever solve this? I have got some queries that use both spatial and non-spatial indexes and with one foreign join everything was fine. As soon as I upped the joins, performance went to hell. However, adding use_remote_estimate 'true' solved my problems. I am running 11.2, though, and a lot has been done since 9.4. – John Powell Apr 22 '19 at 18:25
  • Yeah but if I remember correctly had to use a different solution. Just tested this query on 9.6.10 and 11.1 and problem still exists, use_remote_estimate is set to true on all tables. I did try to run analyse verbose on all foreign tables but that had no effect. – Karlo Petravić Apr 24 '19 at 8:51
-2

Use pev to see how slow.

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)

It's it network latency ?

| improve this answer | |
  • I think the OP wants to know how to improve his statement after adding an additional table via JOIN. He already knows the statement is slow. You might want to consider adding more details to your answer to improve it for the community. – John aka hot2use Apr 3 '18 at 8:00

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.