Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gs49R-0008TS-VD for pgsql-performance@arkaria.postgresql.org; Fri, 08 Feb 2019 11:16:58 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gs49P-0004jq-Ss for pgsql-performance@arkaria.postgresql.org; Fri, 08 Feb 2019 11:16:55 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gs49P-0004jj-5n for pgsql-performance@lists.postgresql.org; Fri, 08 Feb 2019 11:16:55 +0000 Received: from sonic313-21.consmr.mail.ir2.yahoo.com ([77.238.179.188]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gs49G-0004rw-KD for pgsql-performance@postgresql.org; Fri, 08 Feb 2019 11:16:54 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.co.uk; s=s2048; t=1549624604; bh=XrJkmDJxQ96/X9dBRUbDlAYH2h3Tv/XELeD9oGVaWfg=; h=Date:From:To:Subject:References:From:Subject; b=Rf63rjUmuCpt8YxUP7Wej4Th+hAT1WnANIeuH2V0tMeYjFfXLGt1qjyIu6ruems4AFf+pBW66PQNJw5tua3AIAbaOYm7CrHaA/J3xAI+Gicz+ldefBhmOXqXR4NdqOB1BMOfTVoQkERGvwl4ntt5nXQU8HhiOWVUHpXgpQVWBWeQnMJFc+LOXCR20RyLDJKxnjfRvtM/6cBblerhZoHRNhugS/35gdneJILASjbiGizKr4KaNJs221g6kIeAuntac0rX1UuKo+civ+lK/lTF9UMlB/fshWZFoLNKq6cN3FTMIh1hpxRxOx1FGDHB50qHb0H88i4DQLuaBNybDCwmjg== X-YMail-OSG: kWaremAVM1ksWzsBofEZ0ldnjbaj4TwLCZ9N_SMmApjPv1OTl8nFh3mDeGT2Goa PhMRLAKQwOxGwHx.l5_5lHUWo0z6B3H9h2PtNBWkFRTkOm0r0P3C7h8e3fcmojw.mKf_9cGtOyCu YRFMDsXwlL3wtiT71mmRK9ZdDatzKodabr6vIoHW3htIDjHvyWz3Pb_zK44HY_KoL1KnRbOyl1nu zjv9wpYj.wvh5_s1FE_NAQonLfK7187Kfa9xb_OkQUOjW.Eq85vW4Vqj02foxjYa.HmUMSh66i3l hKUF3_fpMPR4JboPnXRAMuraneeBQnnQrwJ58y6MKYYM_SZmuADkg2PCxfDE5Dwxs6mAPikwz3RT 9unxqN05xGe8Y57v33liwwnr8do1_p_Nl950PfIxrenVkN9pi_RjRBAJFslS1JmIAeGUbVlr5tb5 V3RLdQxRYTkHIyftI3qSovXI9_FjwTey3rFO4pcZ.eISPLewUQcu3rQKMUCaUZuu3hcbkkh5FmSH 1DepUwaRlwgIGqFjfPjKQELOTLI6M4ciEttBK1gAn23jQbXhvAhJtU2xwRJk7KhD.cS1nhBREEO9 HL8mKdaW6IwGoMT9vLWijvlccHRBQS9C1KViOY4orMdZfYFO4IHtKuJKkCVSfopVpnMqAxK8QQ0h TD2heX.trVsrw5OYClP4LvZYk8TvRPb8TQcV0ZVq9g4p8IOZZ9crpVF.6i3v3LG8NrMgyYg0EC1r pmc1X2yANDEFhWZQ5FTcYZ6W79X2gW3WBTFbPzJKyDsgb3PeIcl1I0iwKINisheB3BJjgoTsCDRa NbCqHAUnqdg_d6h6UxNk5k8ARnmRZ0vA5JxB2yzDTQyNf6kkIo9gTPEo.SJy.22oL29VIio0PgIG 8RBnIRkfuPgBtLZMnzR8yDpspudJmSGSIfdrKzL8Y9WE9UaoCteGKv.qLvlp1wu.2oXT2mFld4Od 1ELF3p6pxoyD3AP5V9S6QLdXYeyuaNEtqFfbbq9hnVoaXI6u.BRKHNVUjLyC8KifLyZJJ2mRYu8h k8DW0FQ9RJ1zCFWc_Ai6wdd6rZmeZ.sc_aMfOAYIxKT7Rj3woqO1AI6GZ6hyu_zSkF9eM Received: from sonic.gate.mail.ne1.yahoo.com by sonic313.consmr.mail.ir2.yahoo.com with HTTP; Fri, 8 Feb 2019 11:16:44 +0000 Date: Fri, 8 Feb 2019 11:13:51 +0000 (UTC) From: keith anderson To: "pgsql-performance@postgresql.org" Message-ID: <1233297695.414916.1549624431535@mail.yahoo.com> Subject: Partitioning Optimizer Questions and Issues MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_414915_1839076534.1549624431527" References: <1233297695.414916.1549624431535.ref@mail.yahoo.com> X-Mailer: WebService/1.1.13027 YMailNorrin Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/72.0.3626.96 Safari/537.36 Content-Length: 60971 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_414915_1839076534.1549624431527 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable I am using Postgres on a large system (recording approximately 20million tr= ansactions per day). We use partitioning by date to assist with both vacuum= processing time and to archive old data. At the core of the system are rec= ords in 2 different tables detailing different types of activity for moneta= ry transactions (e.g. money in and money out) -> a single transaction has e= ntries in both tables, so to retrieve all details for a single transaction = we need to join the 2 tables. The use of partitioning however has a significant impact on the performance= of retrieving this data. Being relatively new to Postgres I wanted to shar= e my findings and understand how others address them. We run postgres versi= on 9.6 on CentOS, but the same behaviour is apparent in postgres 10.6. The = test case outputs are from version 10.6 running on my Ubuntu machine with d= efault postgres configuration. Below is an example script to populate the test data: =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3Ddrop t= able if exists tablea cascade;drop table if exists tableb cascade; CREATE TABLE tablea (=C2=A0 =C2=A0 id=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 serial,=C2=A0 =C2=A0 reference=C2=A0 =C2=A0 =C2=A0int not null,=C2=A0 = =C2=A0 created=C2=A0 =C2=A0 =C2=A0 =C2=A0date not null)=C2=A0 PARTITION BY = RANGE (created); CREATE TABLE tablea_part1 PARTITION OF tablea=C2=A0 =C2=A0 FOR VALUES FROM = ('2018-01-01') TO ('2018-01-02');CREATE TABLE tablea_part2 PARTITION OF tab= lea=C2=A0 =C2=A0 FOR VALUES FROM ('2018-01-02') TO ('2018-01-03');CREATE TA= BLE tablea_part3 PARTITION OF tablea=C2=A0 =C2=A0 FOR VALUES FROM ('2018-01= -03') TO ('2018-01-04');CREATE TABLE tablea_part4 PARTITION OF tablea=C2=A0= =C2=A0 FOR VALUES FROM ('2018-01-04') TO ('2018-01-05');CREATE TABLE table= a_part5 PARTITION OF tablea=C2=A0 =C2=A0 FOR VALUES FROM ('2018-01-05') TO = ('2018-01-06'); CREATE INDEX tablea_id_1 ON tablea_part1 (id);CREATE INDEX tablea_id_2 ON t= ablea_part2 (id);CREATE INDEX tablea_id_3 ON tablea_part3 (id);CREATE INDEX= tablea_id_4 ON tablea_part4 (id);CREATE INDEX tablea_id_5 ON tablea_part5 = (id);CREATE INDEX tablea_reference_1 ON tablea_part1 (reference);CREATE IND= EX tablea_reference_2 ON tablea_part2 (reference);CREATE INDEX tablea_refer= ence_3 ON tablea_part3 (reference);CREATE INDEX tablea_reference_4 ON table= a_part4 (reference);CREATE INDEX tablea_reference_5 ON tablea_part5 (refere= nce);CREATE INDEX tablea_created_1 ON tablea_part1 (created);CREATE INDEX t= ablea_created_2 ON tablea_part2 (created);CREATE INDEX tablea_created_3 ON = tablea_part3 (created);CREATE INDEX tablea_created_4 ON tablea_part4 (creat= ed);CREATE INDEX tablea_created_5 ON tablea_part5 (created);alter table tab= lea_part1 add CHECK ( created >=3D DATE '2018-01-01' AND created < DATE '20= 18-01-02');alter table tablea_part2 add CHECK ( created >=3D DATE '2018-01-= 02' AND created < DATE '2018-01-03');alter table tablea_part3 add CHECK ( c= reated >=3D DATE '2018-01-03' AND created < DATE '2018-01-04');alter table = tablea_part4 add CHECK ( created >=3D DATE '2018-01-04' AND created < DATE = '2018-01-05');alter table tablea_part5 add CHECK ( created >=3D DATE '2018-= 01-05' AND created < DATE '2018-01-06'); create or replace function populate_tablea()=C2=A0RETURNS integer AS$BODY$= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0DECLAR= E=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0=C2=A0 i i= nteger;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0 v_created= date;BEGIN=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0=C2=A0 =C2=A0 i :=3D 0;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0 =C2=A0 WHILE (i < 50000)=C2=A0 =C2=A0= loop=C2=A0 =C2=A0 =C2=A0 =C2=A0 i :=3D i + 1;=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 IF = (mod(i,5) =3D 1) THEN=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 v_created = =3D '2018-01-01';=C2=A0 =C2=A0 =C2=A0 =C2=A0 ELSIF (mod(i,5) =3D 2) THEN=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 v_created =3D '2018-01-02';=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0ELSIF (mod(i,5) =3D 3) THEN=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 v_created =3D '2018-01-03';=C2=A0 =C2=A0 =C2=A0 =C2=A0= ELSIF (mod(i,5) =3D 4) THEN=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 v_cre= ated =3D '2018-01-04';=C2=A0 =C2=A0 =C2=A0 =C2=A0 ELSIF (mod(i,5) =3D 0) TH= EN=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 v_created =3D '2018-01-05';=C2= =A0 =C2=A0 =C2=A0 =C2=A0 END IF;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0 = =C2=A0 =C2=A0 insert into tablea values (i, i, v_created); =C2=A0 =C2=A0 end loop;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0 =C2=A0 = RETURN i;END;$BODY$LANGUAGE plpgsql VOLATILE=C2=A0 =C2=A0 COST 100; CREATE TABLE tableb(=C2=A0 =C2=A0 id=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0seria= l,=C2=A0 =C2=A0 reference=C2=A0 int not null,=C2=A0 =C2=A0 created=C2=A0 = =C2=A0 date not null) PARTITION BY RANGE (created); CREATE TABLE tableb_part1 PARTITION OF tableb=C2=A0 =C2=A0 FOR VALUES FROM = ('2018-01-01') TO ('2018-01-02');CREATE TABLE tableb_part2 PARTITION OF tab= leb=C2=A0 =C2=A0 FOR VALUES FROM ('2018-01-02') TO ('2018-01-03');CREATE TA= BLE tableb_part3 PARTITION OF tableb=C2=A0 =C2=A0 FOR VALUES FROM ('2018-01= -03') TO ('2018-01-04');CREATE TABLE tableb_part4 PARTITION OF tableb=C2=A0= =C2=A0 FOR VALUES FROM ('2018-01-04') TO ('2018-01-05');CREATE TABLE table= b_part5 PARTITION OF tableb=C2=A0 =C2=A0 FOR VALUES FROM ('2018-01-05') TO = ('2018-01-06'); CREATE INDEX tableb_id_1 ON tableb_part1 (id);CREATE INDEX tableb_id_2 ON t= ableb_part2 (id);CREATE INDEX tableb_id_3 ON tableb_part3 (id);CREATE INDEX= tableb_id_4 ON tableb_part4 (id);CREATE INDEX tableb_id_5 ON tableb_part5 = (id);CREATE INDEX tableb_reference_1 ON tableb_part1 (reference);CREATE IND= EX tableb_reference_2 ON tableb_part2 (reference);CREATE INDEX tableb_refer= ence_3 ON tableb_part3 (reference);CREATE INDEX tableb_reference_4 ON table= b_part4 (reference);CREATE INDEX tableb_reference_5 ON tableb_part5 (refere= nce);CREATE INDEX tableb_created_1 ON tableb_part1 (created);CREATE INDEX t= ableb_created_2 ON tableb_part2 (created);CREATE INDEX tableb_created_3 ON = tableb_part3 (created);CREATE INDEX tableb_created_4 ON tableb_part4 (creat= ed);CREATE INDEX tableb_created_5 ON tableb_part5 (created);alter table tab= leb_part1 add CHECK ( created >=3D DATE '2018-01-01' AND created < DATE '20= 18-01-02');alter table tableb_part2 add CHECK ( created >=3D DATE '2018-01-= 02' AND created < DATE '2018-01-03');alter table tableb_part3 add CHECK ( c= reated >=3D DATE '2018-01-03' AND created < DATE '2018-01-04');alter table = tableb_part4 add CHECK ( created >=3D DATE '2018-01-04' AND created < DATE = '2018-01-05');alter table tableb_part5 add CHECK ( created >=3D DATE '2018-= 01-05' AND created < DATE '2018-01-06'); create or replace function populate_tableb()=C2=A0RETURNS integer AS$BODY$D= ECLARE=C2=A0 i integer;=C2=A0 v_created date;BEGIN=C2=A0 =C2=A0 i :=3D 0;= =C2=A0 =C2=A0 WHILE (i < 50000)=C2=A0 =C2=A0 loop=C2=A0 =C2=A0 =C2=A0 =C2= =A0 i :=3D i + 1;=C2=A0 =C2=A0 =C2=A0 =C2=A0 IF (mod(i,5) =3D 0) THEN=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 v_created =3D '2018-01-01';=C2=A0 =C2= =A0 =C2=A0 =C2=A0 ELSIF (mod(i,5) =3D 1) THEN=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 v_created =3D '2018-01-02';=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= ELSIF (mod(i,5) =3D 2) THEN=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 v_crea= ted =3D '2018-01-03';=C2=A0 =C2=A0 =C2=A0 =C2=A0 ELSIF (mod(i,5) =3D 3) THE= N=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 v_created =3D '2018-01-04';=C2= =A0 =C2=A0 =C2=A0 =C2=A0 ELSIF (mod(i,5) =3D 4) THEN=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 v_created =3D '2018-01-05';=C2=A0 =C2=A0=C2=A0=C2=A0 = =C2=A0 =C2=A0 =C2=A0 END IF;=C2=A0 =C2=A0 =C2=A0 =C2=A0 insert into tableb = values (i, i, v_created);=C2=A0 =C2=A0 end loop;=C2=A0 =C2=A0 RETURN i;END;= $BODY$LANGUAGE plpgsql VOLATILE=C2=A0 =C2=A0 COST 100; select populate_tablea();select populate_tableb();vacuum analyze;=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D So it creates 2 tables, both with 5 partitions (using range partitioning on= the created column). Each partition has 10000 rows in it. Below are some example queries I have run, the outputs of explain analyze f= or each and notes on each of my findings/questions: =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D -- NOTICE IN THE BELOW THAT WE USE A SINGLE ID (ESSENTIALLY THE PRIMARY KEY= ) BUT WE HAVE ESTIMATED 5 ROWS RETURNED. WE SEEM TO BE BASING-- ON PARTITIO= N STATS ONLY AND SUMMING. SO EACH PARTITION ASSUMES ID IS UNIQUE, BUT WITH = 5 PARTITIONS, THE TOTAL ROWS IS 5. explain analyze select * from tablea where id =3D 101;=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0QUERY PLAN=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0-------------------------------= ---------------------------------------------------------------------------= -----------------------=C2=A0Append=C2=A0 (cost=3D0.29..41.51 rows=3D5 widt= h=3D12) (actual time=3D0.027..0.066 rows=3D1 loops=3D1)=C2=A0 =C2=A0->=C2= =A0 Index Scan using tablea_id_1 on tablea_part1=C2=A0 (cost=3D0.29..8.30 r= ows=3D1 width=3D12) (actual time=3D0.026..0.029 rows=3D1 loops=3D1)=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (id =3D 101)=C2=A0 =C2=A0->=C2=A0 In= dex Scan using tablea_id_2 on tablea_part2=C2=A0 (cost=3D0.29..8.30 rows=3D= 1 width=3D12) (actual time=3D0.010..0.010 rows=3D0 loops=3D1)=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0Index Cond: (id =3D 101)=C2=A0 =C2=A0->=C2=A0 Index Sca= n using tablea_id_3 on tablea_part3=C2=A0 (cost=3D0.29..8.30 rows=3D1 width= =3D12) (actual time=3D0.008..0.009 rows=3D0 loops=3D1)=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0Index Cond: (id =3D 101)=C2=A0 =C2=A0->=C2=A0 Index Scan using= tablea_id_4 on tablea_part4=C2=A0 (cost=3D0.29..8.30 rows=3D1 width=3D12) = (actual time=3D0.008..0.008 rows=3D0 loops=3D1)=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0Index Cond: (id =3D 101)=C2=A0 =C2=A0->=C2=A0 Index Scan using tablea= _id_5 on tablea_part5=C2=A0 (cost=3D0.29..8.30 rows=3D1 width=3D12) (actual= time=3D0.007..0.007 rows=3D0 loops=3D1)=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0I= ndex Cond: (id =3D 101)=C2=A0Planning time: 0.875 ms=C2=A0Execution time: 0= .176 ms =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D -- IF WE USE AN IN WITH 10 ID'S WE ESTIMATE 50 ROWS RETURNED INSTEAD OF THE= ACTUAL 10. AGAIN SEEMS TO BE AGGREGATING PARTITION STATISTICS. explain analyze select * from tablea where id in (101,102,103,104,105,106,1= 07,108,109,110);=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 QUERY PLAN=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0--------------------------------------------= ---------------------------------------------------------------------------= ------------=C2=A0Append=C2=A0 (cost=3D0.29..215.13 rows=3D50 width=3D12) (= actual time=3D0.040..0.283 rows=3D10 loops=3D1)=C2=A0 =C2=A0->=C2=A0 Index = Scan using tablea_id_1 on tablea_part1=C2=A0 (cost=3D0.29..43.03 rows=3D10 = width=3D12) (actual time=3D0.039..0.079 rows=3D2 loops=3D1)=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0Index Cond: (id =3D ANY ('{101,102,103,104,105,106,107,= 108,109,110}'::integer[]))=C2=A0 =C2=A0->=C2=A0 Index Scan using tablea_id_= 2 on tablea_part2=C2=A0 (cost=3D0.29..43.03 rows=3D10 width=3D12) (actual t= ime=3D0.021..0.052 rows=3D2 loops=3D1)=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Ind= ex Cond: (id =3D ANY ('{101,102,103,104,105,106,107,108,109,110}'::integer[= ]))=C2=A0 =C2=A0->=C2=A0 Index Scan using tablea_id_3 on tablea_part3=C2=A0= (cost=3D0.29..43.03 rows=3D10 width=3D12) (actual time=3D0.022..0.048 rows= =3D2 loops=3D1)=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (id =3D ANY ('= {101,102,103,104,105,106,107,108,109,110}'::integer[]))=C2=A0 =C2=A0->=C2= =A0 Index Scan using tablea_id_4 on tablea_part4=C2=A0 (cost=3D0.29..43.03 = rows=3D10 width=3D12) (actual time=3D0.026..0.049 rows=3D2 loops=3D1)=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (id =3D ANY ('{101,102,103,104,105,= 106,107,108,109,110}'::integer[]))=C2=A0 =C2=A0->=C2=A0 Index Scan using ta= blea_id_5 on tablea_part5=C2=A0 (cost=3D0.29..43.03 rows=3D10 width=3D12) (= actual time=3D0.028..0.048 rows=3D2 loops=3D1)=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0Index Cond: (id =3D ANY ('{101,102,103,104,105,106,107,108,109,110}':= :integer[]))=C2=A0Planning time: 1.526 ms=C2=A0Execution time: 0.397 ms=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0=C2=A0 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D -- IF WE USE A RANGE INSTEAD OF INDIVIDUAL ID'S, WE GET ESTIMATED 10 ROWS R= ETURNED (GOOD).=C2=A0-- IS THIS USING THE GLOBAL TABLE STATISTICS INSTEAD? = WHY DOES IT DIFFER FROM DISTINCT ID'S? explain analyze select * from tablea where id >=3D 101 and id <=3D 110;=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0QUERY PLAN=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0--------------= ---------------------------------------------------------------------------= ----------------------------------------=C2=A0Append=C2=A0 (cost=3D0.29..41= .62 rows=3D10 width=3D12) (actual time=3D0.022..0.074 rows=3D10 loops=3D1)= =C2=A0 =C2=A0->=C2=A0 Index Scan using tablea_id_1 on tablea_part1=C2=A0 (c= ost=3D0.29..8.32 rows=3D2 width=3D12) (actual time=3D0.021..0.026 rows=3D2 = loops=3D1)=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: ((id >=3D 101) AND = (id <=3D 110))=C2=A0 =C2=A0->=C2=A0 Index Scan using tablea_id_2 on tablea_= part2=C2=A0 (cost=3D0.29..8.32 rows=3D2 width=3D12) (actual time=3D0.010..0= .012 rows=3D2 loops=3D1)=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: ((id = >=3D 101) AND (id <=3D 110))=C2=A0 =C2=A0->=C2=A0 Index Scan using tablea_i= d_3 on tablea_part3=C2=A0 (cost=3D0.29..8.32 rows=3D2 width=3D12) (actual t= ime=3D0.009..0.010 rows=3D2 loops=3D1)=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Ind= ex Cond: ((id >=3D 101) AND (id <=3D 110))=C2=A0 =C2=A0->=C2=A0 Index Scan = using tablea_id_4 on tablea_part4=C2=A0 (cost=3D0.29..8.32 rows=3D2 width= =3D12) (actual time=3D0.009..0.010 rows=3D2 loops=3D1)=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0Index Cond: ((id >=3D 101) AND (id <=3D 110))=C2=A0 =C2=A0->= =C2=A0 Index Scan using tablea_id_5 on tablea_part5=C2=A0 (cost=3D0.29..8.3= 2 rows=3D2 width=3D12) (actual time=3D0.008..0.010 rows=3D2 loops=3D1)=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: ((id >=3D 101) AND (id <=3D 110)= )=C2=A0Planning time: 1.845 ms=C2=A0Execution time: 0.196 ms =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D -- HERE ARE THE TABLE STATS, SHOWING THAT POSTGRES IS AWARE THAT ID'S ARE G= LOBALLY UNIQUE IN THE TABLEA TABLE. CAN IT USE THEM? select tablename,n_distinct from pg_stats where tablename like '%tablea%' a= nd attname =3D 'id';=C2=A0 tablename=C2=A0 =C2=A0| n_distinct=C2=A0--------= ------+------------=C2=A0tablea_part3 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-1= =C2=A0tablea=C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-= 1=C2=A0tablea_part2 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-1=C2=A0tablea_part4= |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-1=C2=A0tablea_part5 |=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0-1=C2=A0tablea_part1 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-1 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D -- WHEN I JOIN, THE NUMBER OF ROWS MULTIPLIES. NOTICE THE SEQUENTIAL SCAN O= F THE TABLEB PARTITION. THIS IS CAUSED BY THE OVERESTIMATION-- OF ROWS RETU= RNED BY TABLEA explain analyze select * from tablea a, tableb b where a.reference =3D b.re= ference and a.id in (101,102,103,104,105,106,107,108,109,110);=C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 QUERY PLAN=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-------------------------------------= ---------------------------------------------------------------------------= -----------------------------------=C2=A0Hash Join=C2=A0 (cost=3D215.75..11= 78.75 rows=3D50 width=3D24) (actual time=3D0.386..46.845 rows=3D10 loops=3D= 1)=C2=A0 =C2=A0Hash Cond: (b.reference =3D a.reference)=C2=A0 =C2=A0->=C2= =A0 Append=C2=A0 (cost=3D0.00..775.00 rows=3D50000 width=3D12) (actual time= =3D0.024..26.527 rows=3D50000 loops=3D1)=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-= >=C2=A0 Seq Scan on tableb_part1 b=C2=A0 (cost=3D0.00..155.00 rows=3D10000 = width=3D12) (actual time=3D0.022..4.006 rows=3D10000 loops=3D1)=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0->=C2=A0 Seq Scan on tableb_part2 b_1=C2=A0 (cost= =3D0.00..155.00 rows=3D10000 width=3D12) (actual time=3D0.023..4.039 rows= =3D10000 loops=3D1)=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0->=C2=A0 Seq Scan on t= ableb_part3 b_2=C2=A0 (cost=3D0.00..155.00 rows=3D10000 width=3D12) (actual= time=3D0.023..3.247 rows=3D10000 loops=3D1)=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0->=C2=A0 Seq Scan on tableb_part4 b_3=C2=A0 (cost=3D0.00..155.00 rows=3D= 10000 width=3D12) (actual time=3D0.016..1.421 rows=3D10000 loops=3D1)=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0->=C2=A0 Seq Scan on tableb_part5 b_4=C2=A0 (co= st=3D0.00..155.00 rows=3D10000 width=3D12) (actual time=3D0.007..1.113 rows= =3D10000 loops=3D1)=C2=A0 =C2=A0->=C2=A0 Hash=C2=A0 (cost=3D215.13..215.13 = rows=3D50 width=3D12) (actual time=3D0.316..0.316 rows=3D10 loops=3D1)=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buckets: 1024=C2=A0 Batches: 1=C2=A0 Memory = Usage: 9kB=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0->=C2=A0 Append=C2=A0 (cost=3D0= .29..215.13 rows=3D50 width=3D12) (actual time=3D0.034..0.301 rows=3D10 loo= ps=3D1)=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0->=C2=A0 Inde= x Scan using tablea_id_1 on tablea_part1 a=C2=A0 (cost=3D0.29..43.03 rows= =3D10 width=3D12) (actual time=3D0.033..0.074 rows=3D2 loops=3D1)=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Con= d: (id =3D ANY ('{101,102,103,104,105,106,107,108,109,110}'::integer[]))=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0->=C2=A0 Index Scan usi= ng tablea_id_2 on tablea_part2 a_1=C2=A0 (cost=3D0.29..43.03 rows=3D10 widt= h=3D12) (actual time=3D0.020..0.051 rows=3D2 loops=3D1)=C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (id =3D= ANY ('{101,102,103,104,105,106,107,108,109,110}'::integer[]))=C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0->=C2=A0 Index Scan using tablea_= id_3 on tablea_part3 a_2=C2=A0 (cost=3D0.29..43.03 rows=3D10 width=3D12) (a= ctual time=3D0.021..0.048 rows=3D2 loops=3D1)=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (id =3D ANY ('{= 101,102,103,104,105,106,107,108,109,110}'::integer[]))=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0->=C2=A0 Index Scan using tablea_id_4 on = tablea_part4 a_3=C2=A0 (cost=3D0.29..43.03 rows=3D10 width=3D12) (actual ti= me=3D0.025..0.072 rows=3D2 loops=3D1)=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (id =3D ANY ('{101,102,10= 3,104,105,106,107,108,109,110}'::integer[]))=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0->=C2=A0 Index Scan using tablea_id_5 on tablea_par= t5 a_4=C2=A0 (cost=3D0.29..43.03 rows=3D10 width=3D12) (actual time=3D0.028= ..0.049 rows=3D2 loops=3D1)=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (id =3D ANY ('{101,102,103,104,105,= 106,107,108,109,110}'::integer[]))=C2=A0Planning time: 2.642 ms=C2=A0Execut= ion time: 47.005 ms =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D -- REPEAT, BUT USING A RANGE QUERY. NO LONGER SEQUENTIAL SCAN AS THE TABLEA= ROW ESTIMATE DROPS TO 10 FROM 50.=C2=A0-- QUERY EXECUTION TIME DROPS FROM = 47MS TO 0.7MS explain analyze select * from tablea a, tableb b where a.reference =3D b.re= ference and a.id >=3D 101 and a. id <=3D 110;=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 QUERY PLAN= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0---------------------------------------------------= ---------------------------------------------------------------------------= ---------------------=C2=A0Nested Loop=C2=A0 (cost=3D0.57..437.25 rows=3D10= width=3D24) (actual time=3D0.063..0.543 rows=3D10 loops=3D1)=C2=A0 =C2=A0-= >=C2=A0 Append=C2=A0 (cost=3D0.29..41.62 rows=3D10 width=3D12) (actual time= =3D0.027..0.091 rows=3D10 loops=3D1)=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0->=C2= =A0 Index Scan using tablea_id_1 on tablea_part1 a=C2=A0 (cost=3D0.29..8.32= rows=3D2 width=3D12) (actual time=3D0.026..0.031 rows=3D2 loops=3D1)=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: ((id >=3D 101)= AND (id <=3D 110))=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0->=C2=A0 Index Scan us= ing tablea_id_2 on tablea_part2 a_1=C2=A0 (cost=3D0.29..8.32 rows=3D2 width= =3D12) (actual time=3D0.010..0.013 rows=3D2 loops=3D1)=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: ((id >=3D 101) AND (id <=3D 1= 10))=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0->=C2=A0 Index Scan using tablea_id_3= on tablea_part3 a_2=C2=A0 (cost=3D0.29..8.32 rows=3D2 width=3D12) (actual = time=3D0.009..0.011 rows=3D2 loops=3D1)=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0Index Cond: ((id >=3D 101) AND (id <=3D 110))=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0->=C2=A0 Index Scan using tablea_id_4 on tablea_par= t4 a_3=C2=A0 (cost=3D0.29..8.32 rows=3D2 width=3D12) (actual time=3D0.009..= 0.012 rows=3D2 loops=3D1)=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0Index Cond: ((id >=3D 101) AND (id <=3D 110))=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0->=C2=A0 Index Scan using tablea_id_5 on tablea_part5 a_4=C2=A0 (= cost=3D0.29..8.32 rows=3D2 width=3D12) (actual time=3D0.015..0.018 rows=3D2= loops=3D1)=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Con= d: ((id >=3D 101) AND (id <=3D 110))=C2=A0 =C2=A0->=C2=A0 Append=C2=A0 (cos= t=3D0.29..39.51 rows=3D5 width=3D12) (actual time=3D0.021..0.041 rows=3D1 l= oops=3D10)=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0->=C2=A0 Index Scan using table= b_reference_1 on tableb_part1 b=C2=A0 (cost=3D0.29..7.90 rows=3D1 width=3D1= 2) (actual time=3D0.007..0.007 rows=3D0 loops=3D10)=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (reference =3D a.reference)=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0->=C2=A0 Index Scan using tableb_reference_2= on tableb_part2 b_1=C2=A0 (cost=3D0.29..7.90 rows=3D1 width=3D12) (actual = time=3D0.006..0.006 rows=3D0 loops=3D10)=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0Index Cond: (reference =3D a.reference)=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0->=C2=A0 Index Scan using tableb_reference_3 on tableb_= part3 b_2=C2=A0 (cost=3D0.29..7.90 rows=3D1 width=3D12) (actual time=3D0.00= 9..0.010 rows=3D0 loops=3D10)=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0Index Cond: (reference =3D a.reference)=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0->=C2=A0 Index Scan using tableb_reference_4 on tableb_part4 b_3= =C2=A0 (cost=3D0.29..7.90 rows=3D1 width=3D12) (actual time=3D0.006..0.007 = rows=3D0 loops=3D10)=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= Index Cond: (reference =3D a.reference)=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0->= =C2=A0 Index Scan using tableb_reference_5 on tableb_part5 b_4=C2=A0 (cost= =3D0.29..7.90 rows=3D1 width=3D12) (actual time=3D0.006..0.006 rows=3D0 loo= ps=3D10)=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: = (reference =3D a.reference)=C2=A0Planning time: 3.629 ms=C2=A0Execution tim= e: 0.762 ms =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D So to summarise the findings/questions from above: - It seems like the Postgres optimizer sometimes uses the partition level s= tatistics, and sometimes the global table level statistics? Or is it using = something else?- With partitioning tables with unique identifier and retrie= ving explicitly on those identifiers, at present the optimizer will always = understimate the selectivity and overestimate the rows returned. This inacc= uracy increases in proportion to the number of partitions.- As a result, wh= en joining to other tables, you are liable to hitting sequential scans. Thi= s becomes more likely as you have more partitions or if join to more partit= ioned tables (note I am aware I could try and tune random_page_cost to try = and prevent this).- To me in the examples queries described above, it makes= sense to use the partition statistics for the partition level access strat= egy, but the global statistics when estimating the actual rows returned by = all the individual partition queries. Is there a reason not to do this? Or = do others believe the optimizer is doing the right thing here? And then some general questions: - How do other people use partitioning but without a significant performanc= e disadvantage on reading the data? Is there something else I should be doi= ng here to achieve the same thing without the overhead? At present my reads= have increased optimization cost (as it needs to optimize access to each p= artition) and also execution cost (access the index on every partition). Ev= en without the optimizer issues described above, the cost of reading simple= data is extremely high relative to non-partitioned data (unless you use th= e partition key as a filter for each table to eliminate those partitions).-= Is there any chance/plan to add global indexes to postgres? If so would th= at impact significantly the cost of the partition drop e.g. to clean up the= index. Thanks in advance for any feedback/support, Keith ------=_Part_414915_1839076534.1549624431527 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
I am using Postgres on a large system (recording approxima= tely 20million transactions per day). We use partitioning by date to assist= with both vacuum processing time and to archive old data. At the core of t= he system are records in 2 different tables detailing different types of ac= tivity for monetary transactions (e.g. money in and money out) -> a sing= le transaction has entries in both tables, so to retrieve all details for a= single transaction we need to join the 2 tables.

<= div>The use of partitioning however has a significant impact on the perform= ance of retrieving this data. Being relatively new to Postgres I wanted to = share my findings and understand how others address them. We run postgres v= ersion 9.6 on CentOS, but the same behaviour is apparent in postgres 10.6. = The test case outputs are from version 10.6 running on my Ubuntu machine wi= th default postgres configuration.

Below is an exa= mple script to populate the test data:

=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
drop= table if exists tablea cascade;
drop table if exists tableb casc= ade;

CREATE TABLE tablea (
   = id            serial,
   = ; reference     int not null,
    create= d       date not null
)  PARTITION BY RA= NGE (created);


CREATE TABLE tablea_= part1 PARTITION OF tablea
    FOR VALUES FROM ('2018-01= -01') TO ('2018-01-02');
CREATE TABLE tablea_part2 PARTITION OF t= ablea
    FOR VALUES FROM ('2018-01-02') TO ('2018-01-0= 3');
CREATE TABLE tablea_part3 PARTITION OF tablea
&nbs= p;   FOR VALUES FROM ('2018-01-03') TO ('2018-01-04');
CREAT= E TABLE tablea_part4 PARTITION OF tablea
    FOR VALUES= FROM ('2018-01-04') TO ('2018-01-05');
CREATE TABLE tablea_part5= PARTITION OF tablea
    FOR VALUES FROM ('2018-01-05')= TO ('2018-01-06');

CREATE INDEX tablea_id_1 ON ta= blea_part1 (id);
CREATE INDEX tablea_id_2 ON tablea_part2 (id);
CREATE INDEX tablea_id_3 ON tablea_part3 (id);
CREATE IN= DEX tablea_id_4 ON tablea_part4 (id);
CREATE INDEX tablea_id_5 ON= tablea_part5 (id);
CREATE INDEX tablea_reference_1 ON tablea_par= t1 (reference);
CREATE INDEX tablea_reference_2 ON tablea_part2 (= reference);
CREATE INDEX tablea_reference_3 ON tablea_part3 (refe= rence);
CREATE INDEX tablea_reference_4 ON tablea_part4 (referenc= e);
CREATE INDEX tablea_reference_5 ON tablea_part5 (reference);<= /div>
CREATE INDEX tablea_created_1 ON tablea_part1 (created);
CREATE INDEX tablea_created_2 ON tablea_part2 (created);
CREATE= INDEX tablea_created_3 ON tablea_part3 (created);
CREATE INDEX t= ablea_created_4 ON tablea_part4 (created);
CREATE INDEX tablea_cr= eated_5 ON tablea_part5 (created);
alter table tablea_part1 add C= HECK ( created >=3D DATE '2018-01-01' AND created < DATE '2018-01-02'= );
alter table tablea_part2 add CHECK ( created >=3D DATE '201= 8-01-02' AND created < DATE '2018-01-03');
alter table tablea_= part3 add CHECK ( created >=3D DATE '2018-01-03' AND created < DATE '= 2018-01-04');
alter table tablea_part4 add CHECK ( created >= =3D DATE '2018-01-04' AND created < DATE '2018-01-05');
alter = table tablea_part5 add CHECK ( created >=3D DATE '2018-01-05' AND create= d < DATE '2018-01-06');

create or replace funct= ion populate_tablea()
 RETURNS integer AS
$BODY$&n= bsp;                     =                      = ;                  
= DECLARE                   = ;                   
  i integer;              &nb= sp;                    
  v_created date;
BEGIN        =                      = ;              
    = i :=3D 0;                  &nb= sp;                    
    WHILE (i < 50000)
    loop
        i :=3D i + 1;       = ;                     &nb= sp;           
     =   IF (mod(i,5) =3D 1) THEN
        &nbs= p;   v_created =3D '2018-01-01';
       = ELSIF (mod(i,5) =3D 2) THEN
          &= nbsp; v_created =3D '2018-01-02';
        &nb= sp;ELSIF (mod(i,5) =3D 3) THEN
         =   v_created =3D '2018-01-03';
        E= LSIF (mod(i,5) =3D 4) THEN
          &nb= sp; v_created =3D '2018-01-04';
        ELSIF= (mod(i,5) =3D 0) THEN
            = v_created =3D '2018-01-05';
        END IF;&n= bsp;                     =       
        insert int= o tablea values (i, i, v_created);

    e= nd loop;                  &nbs= p;                     &n= bsp;                     =                      = ;
           
  &nbs= p; RETURN i;
END;
$BODY$
LANGUAGE plpgsql VOL= ATILE
    COST 100;

CREATE TAB= LE tableb(
    id         seri= al,
    reference  int not null,
  =   created    date not null
) PARTITION BY RANGE (c= reated);


CREATE TABLE tableb_part1 = PARTITION OF tableb
    FOR VALUES FROM ('2018-01-01') = TO ('2018-01-02');
CREATE TABLE tableb_part2 PARTITION OF tableb<= /div>
    FOR VALUES FROM ('2018-01-02') TO ('2018-01-03');
CREATE TABLE tableb_part3 PARTITION OF tableb
  &nb= sp; FOR VALUES FROM ('2018-01-03') TO ('2018-01-04');
CREATE TABL= E tableb_part4 PARTITION OF tableb
    FOR VALUES FROM = ('2018-01-04') TO ('2018-01-05');
CREATE TABLE tableb_part5 PARTI= TION OF tableb
    FOR VALUES FROM ('2018-01-05') TO ('= 2018-01-06');


CREATE INDEX tableb_i= d_1 ON tableb_part1 (id);
CREATE INDEX tableb_id_2 ON tableb_part= 2 (id);
CREATE INDEX tableb_id_3 ON tableb_part3 (id);
= CREATE INDEX tableb_id_4 ON tableb_part4 (id);
CREATE INDEX table= b_id_5 ON tableb_part5 (id);
CREATE INDEX tableb_reference_1 ON t= ableb_part1 (reference);
CREATE INDEX tableb_reference_2 ON table= b_part2 (reference);
CREATE INDEX tableb_reference_3 ON tableb_pa= rt3 (reference);
CREATE INDEX tableb_reference_4 ON tableb_part4 = (reference);
CREATE INDEX tableb_reference_5 ON tableb_part5 (ref= erence);
CREATE INDEX tableb_created_1 ON tableb_part1 (created);=
CREATE INDEX tableb_created_2 ON tableb_part2 (created);
CREATE INDEX tableb_created_3 ON tableb_part3 (created);
CREAT= E INDEX tableb_created_4 ON tableb_part4 (created);
CREATE INDEX = tableb_created_5 ON tableb_part5 (created);
alter table tableb_pa= rt1 add CHECK ( created >=3D DATE '2018-01-01' AND created < DATE '20= 18-01-02');
alter table tableb_part2 add CHECK ( created >=3D = DATE '2018-01-02' AND created < DATE '2018-01-03');
alter tabl= e tableb_part3 add CHECK ( created >=3D DATE '2018-01-03' AND created &l= t; DATE '2018-01-04');
alter table tableb_part4 add CHECK ( creat= ed >=3D DATE '2018-01-04' AND created < DATE '2018-01-05');
alter table tableb_part5 add CHECK ( created >=3D DATE '2018-01-05' AND= created < DATE '2018-01-06');


c= reate or replace function populate_tableb()
 RETURNS integer= AS
$BODY$
DECLARE
  i integer;
  v_created date;
BEGIN
    i :=3D 0;<= /div>
    WHILE (i < 50000)
    loop
        i :=3D i + 1;
    =     IF (mod(i,5) =3D 0) THEN
       = ;     v_created =3D '2018-01-01';
      =   ELSIF (mod(i,5) =3D 1) THEN
        &n= bsp;   v_created =3D '2018-01-02';
      &nbs= p;  ELSIF (mod(i,5) =3D 2) THEN
        =     v_created =3D '2018-01-03';
      &n= bsp; ELSIF (mod(i,5) =3D 3) THEN
        &nbs= p;   v_created =3D '2018-01-04';
       = ELSIF (mod(i,5) =3D 4) THEN
          &= nbsp; v_created =3D '2018-01-05';    
   = ;     END IF;
        insert into t= ableb values (i, i, v_created);
    end loop;
    RETURN i;
END;
$BODY$
LANGUAGE= plpgsql VOLATILE
    COST 100;

select populate_tablea();
select populate_tableb();
v= acuum analyze;
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

=

So it creates 2 tables, both with 5 partitions (using r= ange partitioning on the created column). Each partition has 10000 rows in = it.

Below are some example queries I have run, the= outputs of explain analyze for each and notes on each of my findings/quest= ions:


=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D

-- NOTICE IN THE BELOW THAT WE USE A SIN= GLE ID (ESSENTIALLY THE PRIMARY KEY) BUT WE HAVE ESTIMATED 5 ROWS RETURNED.= WE SEEM TO BE BASING
-- ON PARTITION STATS ONLY AND SUMMING. SO = EACH PARTITION ASSUMES ID IS UNIQUE, BUT WITH 5 PARTITIONS, THE TOTAL ROWS = IS 5.

explain analyze select * from tablea where i= d =3D 101;
               = ;                     &nb= sp;                     &= nbsp;QUERY PLAN                &nbs= p;                     &n= bsp;                    &= nbsp;
-----------------------------------------------------------= ----------------------------------------------------------------------
 Append  (cost=3D0.29..41.51 rows=3D5 width=3D12) (actual t= ime=3D0.027..0.066 rows=3D1 loops=3D1)
   ->  I= ndex Scan using tablea_id_1 on tablea_part1  (cost=3D0.29..8.30 rows= =3D1 width=3D12) (actual time=3D0.026..0.029 rows=3D1 loops=3D1)
=          Index Cond: (id =3D 101)
 =  ->  Index Scan using tablea_id_2 on tablea_part2  (cost= =3D0.29..8.30 rows=3D1 width=3D12) (actual time=3D0.010..0.010 rows=3D0 loo= ps=3D1)
         Index Cond: (id =3D 101= )
   ->  Index Scan using tablea_id_3 on tablea= _part3  (cost=3D0.29..8.30 rows=3D1 width=3D12) (actual time=3D0.008..= 0.009 rows=3D0 loops=3D1)
         Index= Cond: (id =3D 101)
   ->  Index Scan using tab= lea_id_4 on tablea_part4  (cost=3D0.29..8.30 rows=3D1 width=3D12) (act= ual time=3D0.008..0.008 rows=3D0 loops=3D1)
      =    Index Cond: (id =3D 101)
   ->  In= dex Scan using tablea_id_5 on tablea_part5  (cost=3D0.29..8.30 rows=3D= 1 width=3D12) (actual time=3D0.007..0.007 rows=3D0 loops=3D1)
&nb= sp;        Index Cond: (id =3D 101)
 Pla= nning time: 0.875 ms
 Execution time: 0.176 ms

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
<= br>
-- IF WE USE AN IN WITH 10 ID'S WE ESTIMATE 50 ROWS RETURNED = INSTEAD OF THE ACTUAL 10. AGAIN SEEMS TO BE AGGREGATING PARTITION STATISTIC= S.

explain analyze select * from tablea where id i= n (101,102,103,104,105,106,107,108,109,110);    
&= nbsp;                    =                     &nbs= p;                 QUERY PLAN =                     &nbs= p;                     &n= bsp;                
----= ---------------------------------------------------------------------------= ----------------------------------------------------
 Append=   (cost=3D0.29..215.13 rows=3D50 width=3D12) (actual time=3D0.040..0.2= 83 rows=3D10 loops=3D1)
   ->  Index Scan using= tablea_id_1 on tablea_part1  (cost=3D0.29..43.03 rows=3D10 width=3D12= ) (actual time=3D0.039..0.079 rows=3D2 loops=3D1)
    &= nbsp;    Index Cond: (id =3D ANY ('{101,102,103,104,105,106,107,1= 08,109,110}'::integer[]))
   ->  Index Scan usi= ng tablea_id_2 on tablea_part2  (cost=3D0.29..43.03 rows=3D10 width=3D= 12) (actual time=3D0.021..0.052 rows=3D2 loops=3D1)
   =      Index Cond: (id =3D ANY ('{101,102,103,104,105,106,107= ,108,109,110}'::integer[]))
   ->  Index Scan u= sing tablea_id_3 on tablea_part3  (cost=3D0.29..43.03 rows=3D10 width= =3D12) (actual time=3D0.022..0.048 rows=3D2 loops=3D1)
  &nb= sp;      Index Cond: (id =3D ANY ('{101,102,103,104,105,106,= 107,108,109,110}'::integer[]))
   ->  Index Sca= n using tablea_id_4 on tablea_part4  (cost=3D0.29..43.03 rows=3D10 wid= th=3D12) (actual time=3D0.026..0.049 rows=3D2 loops=3D1)
  &= nbsp;      Index Cond: (id =3D ANY ('{101,102,103,104,105,10= 6,107,108,109,110}'::integer[]))
   ->  Index S= can using tablea_id_5 on tablea_part5  (cost=3D0.29..43.03 rows=3D10 w= idth=3D12) (actual time=3D0.028..0.048 rows=3D2 loops=3D1)
 =        Index Cond: (id =3D ANY ('{101,102,103,104,105,= 106,107,108,109,110}'::integer[]))
 Planning time: 1.526 ms<= /div>
 Execution time: 0.397 ms
      &nb= sp;                     &= nbsp;                   <= /div>

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

-- IF WE USE A RANGE INSTEAD OF INDIVIDUAL ID'S, WE GET ESTIMATED = 10 ROWS RETURNED (GOOD). 
-- IS THIS USING THE GLOBAL TABLE = STATISTICS INSTEAD? WHY DOES IT DIFFER FROM DISTINCT ID'S?

explain analyze select * from tablea where id >=3D 101 and id &= lt;=3D 110;
              &nbs= p;                     &n= bsp;                     =  QUERY PLAN                &nb= sp;                     &= nbsp;                    =  
----------------------------------------------------------= -----------------------------------------------------------------------
 Append  (cost=3D0.29..41.62 rows=3D10 width=3D12) (actual= time=3D0.022..0.074 rows=3D10 loops=3D1)
   -> = ; Index Scan using tablea_id_1 on tablea_part1  (cost=3D0.29..8.32 row= s=3D2 width=3D12) (actual time=3D0.021..0.026 rows=3D2 loops=3D1)
         Index Cond: ((id >=3D 101) AND (id &l= t;=3D 110))
   ->  Index Scan using tablea_id_2= on tablea_part2  (cost=3D0.29..8.32 rows=3D2 width=3D12) (actual time= =3D0.010..0.012 rows=3D2 loops=3D1)
        &= nbsp;Index Cond: ((id >=3D 101) AND (id <=3D 110))
  &= nbsp;->  Index Scan using tablea_id_3 on tablea_part3  (cost= =3D0.29..8.32 rows=3D2 width=3D12) (actual time=3D0.009..0.010 rows=3D2 loo= ps=3D1)
         Index Cond: ((id >= =3D 101) AND (id <=3D 110))
   ->  Index Sca= n using tablea_id_4 on tablea_part4  (cost=3D0.29..8.32 rows=3D2 width= =3D12) (actual time=3D0.009..0.010 rows=3D2 loops=3D1)
  &nb= sp;      Index Cond: ((id >=3D 101) AND (id <=3D 110))=
   ->  Index Scan using tablea_id_5 on tablea_= part5  (cost=3D0.29..8.32 rows=3D2 width=3D12) (actual time=3D0.008..0= .010 rows=3D2 loops=3D1)
         Index = Cond: ((id >=3D 101) AND (id <=3D 110))
 Planning time= : 1.845 ms
 Execution time: 0.196 ms

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

-- HERE ARE THE T= ABLE STATS, SHOWING THAT POSTGRES IS AWARE THAT ID'S ARE GLOBALLY UNIQUE IN= THE TABLEA TABLE. CAN IT USE THEM?

select tablena= me,n_distinct from pg_stats where tablename like '%tablea%' and attname =3D= 'id';
  tablename   | n_distinct 
= --------------+------------
 tablea_part3 |    &nb= sp;    -1
 tablea       | = ;        -1
 tablea_part2 |   =      -1
 tablea_part4 |      =    -1
 tablea_part5 |        &= nbsp;-1
 tablea_part1 |         -1<= /div>

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

-- WHEN I JOIN, THE NUMBER OF ROWS MULTIPLIES. NOTICE THE SEQUENTIAL = SCAN OF THE TABLEB PARTITION. THIS IS CAUSED BY THE OVERESTIMATION
-- OF ROWS RETURNED BY TABLEA

explain analyze se= lect * from tablea a, tableb b where a.reference =3D b.reference and a.id i= n (101,102,103,104,105,106,107,108,109,110);
     =                     &nbs= p;                     &n= bsp;                   QUERY P= LAN                    &n= bsp;                     =                      = ;      
------------------------------------------= ---------------------------------------------------------------------------= ------------------------------
 Hash Join  (cost=3D215.= 75..1178.75 rows=3D50 width=3D24) (actual time=3D0.386..46.845 rows=3D10 lo= ops=3D1)
   Hash Cond: (b.reference =3D a.reference)
   ->  Append  (cost=3D0.00..775.00 rows=3D5= 0000 width=3D12) (actual time=3D0.024..26.527 rows=3D50000 loops=3D1)
=
         ->  Seq Scan on tableb_part1= b  (cost=3D0.00..155.00 rows=3D10000 width=3D12) (actual time=3D0.022= ..4.006 rows=3D10000 loops=3D1)
         = ;->  Seq Scan on tableb_part2 b_1  (cost=3D0.00..155.00 rows= =3D10000 width=3D12) (actual time=3D0.023..4.039 rows=3D10000 loops=3D1)
         ->  Seq Scan on tableb_pa= rt3 b_2  (cost=3D0.00..155.00 rows=3D10000 width=3D12) (actual time=3D= 0.023..3.247 rows=3D10000 loops=3D1)
        =  ->  Seq Scan on tableb_part4 b_3  (cost=3D0.00..155.00 r= ows=3D10000 width=3D12) (actual time=3D0.016..1.421 rows=3D10000 loops=3D1)=
         ->  Seq Scan on tableb= _part5 b_4  (cost=3D0.00..155.00 rows=3D10000 width=3D12) (actual time= =3D0.007..1.113 rows=3D10000 loops=3D1)
   ->  = Hash  (cost=3D215.13..215.13 rows=3D50 width=3D12) (actual time=3D0.31= 6..0.316 rows=3D10 loops=3D1)
         B= uckets: 1024  Batches: 1  Memory Usage: 9kB
  &nbs= p;      ->  Append  (cost=3D0.29..215.13 rows= =3D50 width=3D12) (actual time=3D0.034..0.301 rows=3D10 loops=3D1)
               ->  Index = Scan using tablea_id_1 on tablea_part1 a  (cost=3D0.29..43.03 rows=3D1= 0 width=3D12) (actual time=3D0.033..0.074 rows=3D2 loops=3D1)
&nb= sp;                    In= dex Cond: (id =3D ANY ('{101,102,103,104,105,106,107,108,109,110}'::integer= []))
               ->=   Index Scan using tablea_id_2 on tablea_part2 a_1  (cost=3D0.29.= .43.03 rows=3D10 width=3D12) (actual time=3D0.020..0.051 rows=3D2 loops=3D1= )
                  =    Index Cond: (id =3D ANY ('{101,102,103,104,105,106,107,108,109= ,110}'::integer[]))
            &nb= sp;  ->  Index Scan using tablea_id_3 on tablea_part3 a_2 = ; (cost=3D0.29..43.03 rows=3D10 width=3D12) (actual time=3D0.021..0.048 row= s=3D2 loops=3D1)
             =        Index Cond: (id =3D ANY ('{101,102,103,104,105,= 106,107,108,109,110}'::integer[]))
        &n= bsp;      ->  Index Scan using tablea_id_4 on tablea= _part4 a_3  (cost=3D0.29..43.03 rows=3D10 width=3D12) (actual time=3D0= .025..0.072 rows=3D2 loops=3D1)
         = ;            Index Cond: (id =3D ANY ('{101,1= 02,103,104,105,106,107,108,109,110}'::integer[]))
    &= nbsp;          ->  Index Scan using tablea= _id_5 on tablea_part5 a_4  (cost=3D0.29..43.03 rows=3D10 width=3D12) (= actual time=3D0.028..0.049 rows=3D2 loops=3D1)
    &nbs= p;                Index Cond: (id = =3D ANY ('{101,102,103,104,105,106,107,108,109,110}'::integer[]))
 Planning time: 2.642 ms
 Execution time: 47.005 ms

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

-- REPEAT, BUT USING A RANGE QUERY. NO LONGER SEQUENTIAL SCAN AS THE= TABLEA ROW ESTIMATE DROPS TO 10 FROM 50. 
-- QUERY EXECUTIO= N TIME DROPS FROM 47MS TO 0.7MS

explain analyze se= lect * from tablea a, tableb b where a.reference =3D b.reference and a.id &= gt;=3D 101 and a. id <=3D 110;
        &nb= sp;                     &= nbsp;                    =                 QUERY PLAN  &= nbsp;                    =                     &nbs= p;                     &n= bsp;  
-----------------------------------------------------= ---------------------------------------------------------------------------= -------------------
 Nested Loop  (cost=3D0.57..437.25 = rows=3D10 width=3D24) (actual time=3D0.063..0.543 rows=3D10 loops=3D1)
   ->  Append  (cost=3D0.29..41.62 rows=3D10 w= idth=3D12) (actual time=3D0.027..0.091 rows=3D10 loops=3D1)
 = ;        ->  Index Scan using tablea_id_1 on ta= blea_part1 a  (cost=3D0.29..8.32 rows=3D2 width=3D12) (actual time=3D0= .026..0.031 rows=3D2 loops=3D1)
         = ;      Index Cond: ((id >=3D 101) AND (id <=3D 110))
         ->  Index Scan using tab= lea_id_2 on tablea_part2 a_1  (cost=3D0.29..8.32 rows=3D2 width=3D12) = (actual time=3D0.010..0.013 rows=3D2 loops=3D1)
    &nb= sp;          Index Cond: ((id >=3D 101) AND (id= <=3D 110))
         ->  Inde= x Scan using tablea_id_3 on tablea_part3 a_2  (cost=3D0.29..8.32 rows= =3D2 width=3D12) (actual time=3D0.009..0.011 rows=3D2 loops=3D1)
=                Index Cond: ((id >= ;=3D 101) AND (id <=3D 110))
         = ;->  Index Scan using tablea_id_4 on tablea_part4 a_3  (cost= =3D0.29..8.32 rows=3D2 width=3D12) (actual time=3D0.009..0.012 rows=3D2 loo= ps=3D1)
               In= dex Cond: ((id >=3D 101) AND (id <=3D 110))
    &= nbsp;    ->  Index Scan using tablea_id_5 on tablea_part5= a_4  (cost=3D0.29..8.32 rows=3D2 width=3D12) (actual time=3D0.015..0.= 018 rows=3D2 loops=3D1)
           =    Index Cond: ((id >=3D 101) AND (id <=3D 110))
   ->  Append  (cost=3D0.29..39.51 rows=3D5 width= =3D12) (actual time=3D0.021..0.041 rows=3D1 loops=3D10)
  &n= bsp;      ->  Index Scan using tableb_reference_1 on= tableb_part1 b  (cost=3D0.29..7.90 rows=3D1 width=3D12) (actual time= =3D0.007..0.007 rows=3D0 loops=3D10)
        =        Index Cond: (reference =3D a.reference)
         ->  Index Scan using tableb_ref= erence_2 on tableb_part2 b_1  (cost=3D0.29..7.90 rows=3D1 width=3D12) = (actual time=3D0.006..0.006 rows=3D0 loops=3D10)
    &n= bsp;          Index Cond: (reference =3D a.referen= ce)
         ->  Index Scan usin= g tableb_reference_3 on tableb_part3 b_2  (cost=3D0.29..7.90 rows=3D1 = width=3D12) (actual time=3D0.009..0.010 rows=3D0 loops=3D10)
&nbs= p;              Index Cond: (reference = =3D a.reference)
         ->  In= dex Scan using tableb_reference_4 on tableb_part4 b_3  (cost=3D0.29..7= .90 rows=3D1 width=3D12) (actual time=3D0.006..0.007 rows=3D0 loops=3D10)
               Index Cond:= (reference =3D a.reference)
         -&= gt;  Index Scan using tableb_reference_5 on tableb_part5 b_4  (co= st=3D0.29..7.90 rows=3D1 width=3D12) (actual time=3D0.006..0.006 rows=3D0 l= oops=3D10)
               = ;Index Cond: (reference =3D a.reference)
 Planning time: 3.6= 29 ms
 Execution time: 0.762 ms

=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D


So t= o summarise the findings/questions from above:

- I= t seems like the Postgres optimizer sometimes uses the partition level stat= istics, and sometimes the global table level statistics? Or is it using som= ething else?
- With partitioning tables with unique identifier an= d retrieving explicitly on those identifiers, at present the optimizer will= always understimate the selectivity and overestimate the rows returned. Th= is inaccuracy increases in proportion to the number of partitions.
- As a result, when joining to other tables, you are liable to hitting se= quential scans. This becomes more likely as you have more partitions or if = join to more partitioned tables (note I am aware I could try and tune rando= m_page_cost to try and prevent this).
- To me in the examples que= ries described above, it makes sense to use the partition statistics for th= e partition level access strategy, but the global statistics when estimatin= g the actual rows returned by all the individual partition queries. Is ther= e a reason not to do this? Or do others believe the optimizer is doing the = right thing here?

And then some general questions:=

- How do other people use partitioning but withou= t a significant performance disadvantage on reading the data? Is there some= thing else I should be doing here to achieve the same thing without the ove= rhead? At present my reads have increased optimization cost (as it needs to= optimize access to each partition) and also execution cost (access the ind= ex on every partition). Even without the optimizer issues described above, = the cost of reading simple data is extremely high relative to non-partition= ed data (unless you use the partition key as a filter for each table to eli= minate those partitions).
- Is there any chance/plan to add globa= l indexes to postgres? If so would that impact significantly the cost of th= e partition drop e.g. to clean up the index.

Thank= s in advance for any feedback/support,

Keith
=


------=_Part_414915_1839076534.1549624431527--