Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qEYLE-0005f3-IB for pgsql-general@arkaria.postgresql.org; Wed, 28 Jun 2023 16:48:28 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qEYLD-0002VF-Cw for pgsql-general@arkaria.postgresql.org; Wed, 28 Jun 2023 16:48:27 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qEYLC-0002V6-TZ for pgsql-general@lists.postgresql.org; Wed, 28 Jun 2023 16:48:27 +0000 Received: from mail-wr1-x429.google.com ([2a00:1450:4864:20::429]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qEYLA-000wwk-4l for pgsql-general@lists.postgresql.org; Wed, 28 Jun 2023 16:48:25 +0000 Received: by mail-wr1-x429.google.com with SMTP id ffacd0b85a97d-313e1c27476so1148393f8f.1 for ; Wed, 28 Jun 2023 09:48:23 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20221208.gappssmtp.com; s=20221208; t=1687970901; x=1690562901; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:from:to:cc:subject:date :message-id:reply-to; bh=fxqEW8Q48FonebZzZb+IjVbONMiVeW7QJZ+CQwTEhd8=; b=zsSutKLdcd5YzbqFJkdHGnv3D73HJyqJ+jSJLYaFxKSknkEaUq7E1qNFjS/zU6vvSm 8qFM8e/pJmA7FDShcEz1ZBhb6MnomYrGh0PT2U31dN33ApzsLHAoZz/YZ3wJ/EQEZX2S SgbXz6zohbVzkleOMhfQTltxJJAw77y13FZYBVDc0DOF5faJ7Phj1xGtV5Glkeh+nCJe EsYw/4HB8/09O+IGPISsVTGU+vHoQboSmkboGq1ebt5ODwLu2JJQhVpsD0KmuklVRU7i f5Ua+M73pPEGC/Y4Jcn54glFhoeqc9kl4sjaJ8ZnDXh/wLkfmTkmHkBvOI9XNTSPXWWo nbBA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1687970901; x=1690562901; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:x-gm-message-state:from :to:cc:subject:date:message-id:reply-to; bh=fxqEW8Q48FonebZzZb+IjVbONMiVeW7QJZ+CQwTEhd8=; b=gR2uYvoiB52sBxl7kWpEOOHNM0SjI6RKj1+wwEEFO+OCsJp+FdwgRrR4c45chkvSUS 89lFm8Wt3c3K5jc/NFa9DJvlOZ/QP+Z5aP9LwCYjCoB/9PSjKMX7HPwJmLMBPXo58Vzs pBOIbSfmhT8bmAR869vI6KFEEhFS1+kYlZOVX37iO04pvNfW80PiVVM0Xvc+Czy8FduH 55pI/t8BnjsbKEabYV+7N+9YnA/cY8Eh2K87HUVbxGBx36iFm5Dy54gdxAtonBcpWplN ek+WJeq86J1ZcU62Ckxx6BgMOVwqmTHQPf0WVi3LTs0PBk5L2bBvhwjj0j57FBnHSWdl qDeQ== X-Gm-Message-State: AC+VfDwXOcLQYsXxZxMWQV6/RGXaSjDWSrBVpn8AOEJEZwm6biZbH/SV /EruQ5hrz6UXXEEW+LCbh4cbGpD2wDJAMOOFy6s= X-Google-Smtp-Source: ACHHUZ4XNlvZuqeFD54823Qx85vnvhnPb12mngpp0Xa6ozSKWkRrUFpL/krQXd1Xs7ZW4t9vSsFZPQ== X-Received: by 2002:a5d:524e:0:b0:314:9dc:4c4 with SMTP id k14-20020a5d524e000000b0031409dc04c4mr2303702wrc.2.1687970901440; Wed, 28 Jun 2023 09:48:21 -0700 (PDT) Received: from localhost.localdomain (121.173.62.188.dynamic.wline.res.cust.swisscom.ch. [188.62.173.121]) by smtp.gmail.com with ESMTPSA id c1-20020a056000104100b0031402a910edsm4414375wrx.86.2023.06.28.09.48.21 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Wed, 28 Jun 2023 09:48:21 -0700 (PDT) Message-ID: <02665fc681ba77695ecf44bf1156e22ac09ae91e.camel@cybertec.at> Subject: Re: need explanation about an explain plan From: Laurenz Albe To: Marc Millas , "pgsql-general@lists.postgresql.org" Date: Wed, 28 Jun 2023 18:48:20 +0200 In-Reply-To: References: Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.46.4 (3.46.4-1.fc37) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Wed, 2023-06-28 at 17:29 +0200, Marc Millas wrote: > https://explain.depesz.com/s/Opk0 >=20 > The big table (10 billions raws) is split in around 130 partitions, one b= y month. > the ladate column is the partition key, and it does have 1 value for each= partition. > there is an index on the numfic column. the distribution of values for th= at column may differ before and after 2019 january. >=20 > The request is executed in a loop for all ten years of data, year 1,2,3, = then year2,3,4 etc > that request is also executed for columns other than cod, one column at a= time (there are 107 columns...) >=20 > I dont=C2=A0understand=C2=A0why the planner use a filter on the date, as = its already within the partition structure. If the grouping column is the partitioning key, try to set enable_partition= wise_join =3D on. Otherwise, your best bet is to create an index that covers both WHERE condi= tions, or a covering index, which will get you the best result: CREATE INDEX ON table1 (numfic, ladate) INCLUDE (cod, nb_obs); Yours, Laurenz Albe