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 1kttra-0005uV-4Y for pgsql-performance@arkaria.postgresql.org; Mon, 28 Dec 2020 14:51:10 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kttrY-0001As-LF for pgsql-performance@arkaria.postgresql.org; Mon, 28 Dec 2020 14:51:08 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kttrY-0001Al-Be for pgsql-performance@lists.postgresql.org; Mon, 28 Dec 2020 14:51:08 +0000 Received: from mira.cbaines.net ([212.71.252.8]) by magus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kttrT-0005sj-Pc for pgsql-performance@lists.postgresql.org; Mon, 28 Dec 2020 14:51:07 +0000 Received: from localhost (92.41.186.20.threembb.co.uk [92.41.186.20]) by mira.cbaines.net (Postfix) with ESMTPSA id EBF5627BC04 for ; Mon, 28 Dec 2020 14:51:01 +0000 (GMT) Received: from capella (localhost [127.0.0.1]) by localhost (OpenSMTPD) with ESMTP id 2b228f5a for ; Mon, 28 Dec 2020 14:50:58 +0000 (UTC) User-agent: mu4e 1.4.13; emacs 27.1 From: Christopher Baines To: pgsql-performance@lists.postgresql.org Subject: Slow recursive CTE query questions, with row estimate and n_distinct issues Date: Mon, 28 Dec 2020 14:50:55 +0000 Message-ID: <87sg7q9e7k.fsf@cbaines.net> MIME-Version: 1.0 Content-Type: multipart/signed; boundary="=-=-="; micalg=pgp-sha512; protocol="application/pgp-signature" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --=-=-= Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Hi, I have a performance problem with a query. I've uploaded it, along with the EXPLAIN ANALYZE output here [1]. 1: https://explain.depesz.com/s/w5vP I think the query is taking longer than I'd like, as PostgreSQL is not generating a great plan, in particular the estimated rows in parts of the plan are way off from the actual number of rows produced, and I think PostgreSQL might pick a better approach if it made more reasonable estimates. Looking at the row numbers on [1], I think things start to go wrong at row 11. The first part of the recursive CTE is estimated to generate 22,122 rows, and this isn't far off the 15,670 rows it generates. The WorkTable scan expects to generate 10x that though, at 221,220. Maybe that's reasonable, I'm unsure? Things seem to continue getting out of hand from this point. The nested loop on line 10 generates 232x less rows than expected, and this results in an overall overestimate of 4,317x. My theory as to why the row estimates is way off is that the planner is using the n_distinct counts for the derivation_inputs table, and they are way off [2]. I've set the stats target to the highest value for this table, so I'm unsure what I can do to improve these estimates? I've included relevant numbers for the two important tables below [2][3], as well as descriptions of the tables [4][5]. Does anyone know if my theory as to why the row estimates in the plan is right, or have any ideas as how I can make the query more performant? Thanks, Chris 2:=20 derivation_inputs: COUNT(*): 285422539 reltuples: 285422528 derivation_id: COUNT(DISTINCT): 7508610 n_distinct: 4336644 (~57% of the true value) derivation_output_id: COUNT(DISTINCT): 5539406 n_distinct: 473762 (~8% of the true value) derivation_outputs: COUNT(*): 8206019 reltuples: 8205873 3: guix_data_service=3D> SELECT attname, n_distinct::integer FROM pg_stats WHE= RE tablename =3D 'derivation_outputs'; attname | n_distinct=20 =2D-----------------------------+------------ derivation_id | -1 name | 54 derivation_output_details_id | 372225 id | -1 (4 rows) 4: guix_data_service@[local]:5432/patches_guix_data_service> \d+ derivation_in= puts Table "guix_data_service.derivation_inputs" =E2=94=8C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=AC=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=AC=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=90 =E2=94=82 Column =E2=94=82 Type =E2=94=82 Collation =E2=94= =82 Nullable =E2=94=82 Default =E2=94=82 Storage =E2=94=82 Stats target =E2= =94=82 Description =E2=94=82 =E2=94=9C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=BC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =BC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=BC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =BC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=A4 =E2=94=82 derivation_id =E2=94=82 integer =E2=94=82 =E2=94= =82 not null =E2=94=82 =E2=94=82 plain =E2=94=82 10000 =E2= =94=82 =E2=94=82 =E2=94=82 derivation_output_id =E2=94=82 integer =E2=94=82 =E2=94= =82 not null =E2=94=82 =E2=94=82 plain =E2=94=82 10000 =E2= =94=82 =E2=94=82 =E2=94=94=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=B4=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=B4=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=B4=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=B4=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =B4=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=B4=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =B4=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=98 Indexes: "derivation_inputs_pkey" PRIMARY KEY, btree (derivation_id, derivation_= output_id) WITH (fillfactor=3D'100') "derivation_inputs_derivation_output_id_idx" btree (derivation_output_i= d) WITH (fillfactor=3D'100') Foreign-key constraints: "derivation_id_fk" FOREIGN KEY (derivation_id) REFERENCES derivations(i= d) "derivation_output_id_fk" FOREIGN KEY (derivation_output_id) REFERENCES= derivation_outputs(id) Access method: heap Options: autovacuum_vacuum_scale_factor=3D0.01, autovacuum_analyze_scale_fa= ctor=3D0.01 5: guix_data_service@[local]:5432/patches_guix_data_service> \d+ derivation_ou= tputs Table "guix_data_service= .derivation_outputs" =E2=94=8C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=AC=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=AC=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=AC= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=AC=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=AC=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=90 =E2=94=82 Column =E2=94=82 Type =E2=94= =82 Collation =E2=94=82 Nullable =E2=94=82 Default =E2= =94=82 Storage =E2=94=82 Stats target =E2=94=82 Description =E2=94=82 =E2=94=9C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=BC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =BC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=A4 =E2=94=82 derivation_id =E2=94=82 integer =E2=94= =82 =E2=94=82 not null =E2=94=82 =E2= =94=82 plain =E2=94=82 =E2=94=82 =E2=94=82 =E2=94=82 name =E2=94=82 character varying =E2=94= =82 =E2=94=82 not null =E2=94=82 =E2= =94=82 extended =E2=94=82 =E2=94=82 =E2=94=82 =E2=94=82 derivation_output_details_id =E2=94=82 integer =E2=94= =82 =E2=94=82 not null =E2=94=82 =E2= =94=82 plain =E2=94=82 =E2=94=82 =E2=94=82 =E2=94=82 id =E2=94=82 integer =E2=94= =82 =E2=94=82 not null =E2=94=82 generated always as identity =E2= =94=82 plain =E2=94=82 =E2=94=82 =E2=94=82 =E2=94=94=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=B4=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=B4=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=B4=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=B4= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=B4=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =B4=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=B4=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=98 Indexes: "derivation_outputs_pkey" PRIMARY KEY, btree (derivation_id, name) "derivation_outputs_derivation_id_idx" btree (derivation_id) "derivation_outputs_derivation_output_details_id_idx" btree (derivation= _output_details_id) "derivation_outputs_unique_id" UNIQUE CONSTRAINT, btree (id) Foreign-key constraints: "derivation_outputs_derivation_id_fk" FOREIGN KEY (derivation_id) REFER= ENCES derivations(id) "derivation_outputs_derivation_output_details_id_fk" FOREIGN KEY (deriv= ation_output_details_id) REFERENCES derivation_output_details(id) Referenced by: TABLE "derivation_inputs" CONSTRAINT "derivation_output_id_fk" FOREIGN = KEY (derivation_output_id) REFERENCES derivation_outputs(id) Access method: heap Options: autovacuum_vacuum_scale_factor=3D0.01, autovacuum_analyze_scale_fa= ctor=3D0.01 --=-=-= Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQKlBAEBCgCPFiEEPonu50WOcg2XVOCyXiijOwuE9XcFAl/p8M9fFIAAAAAALgAo aXNzdWVyLWZwckBub3RhdGlvbnMub3BlbnBncC5maWZ0aGhvcnNlbWFuLm5ldDNF ODlFRUU3NDU4RTcyMEQ5NzU0RTBCMjVFMjhBMzNCMEI4NEY1NzcRHG1haWxAY2Jh aW5lcy5uZXQACgkQXiijOwuE9Xc55g//Za/GYFiHuNxIILEAuv0DaezY5bhKwyyI BZhfbgf/tzVPvj0mfJzrW5hnRK46GM+fCGf8x6oFkDAQ8YLsortKQWWY1UzICZuq GYcG92sYHXea0BL0gGemjO0/Z90YCnRi3cWwRusjf//AGt16styoArC05fUslL+C gQs/c+5wN1EQ6sQttykynjfkOafU44Xxc0NNOJmMevx1XjXsZCqyIU67ZUymNeUp cX4wMbkNqjw0wR7zgmPvfLl1kh4il0wqmqGK/iDe3LFlqOvvww4RQ1FKDHrU/JAF eCwJ9AUiIof7Z4/kXAvLRw4FBCrClk/+G4Bhl/kGfkm6FJGz/sn/AEtNtnq+d46q aCaLgcpTsGyFo2MbNyt1TpE4WnsBoHzUWl1m5yZAwSfSpp6kKTwTvI/qAnHAaWCY TC009Mm8eoXr03jNKak19wxZxQPGX4sgpbn8G7JcCBh06eMMwvwIPPDPZv+aSJdG aLAa060xfQ2++IRU8H3pn7kAfTdfX1i7i6UThuvTkdcowmZJDgNyPmo02kpdWf2a m9+iyRvpKaeJNjLGwwh4QCBLp4KTA9VX32McyfPl+lGYWm03Q2h+5VsFaq24pyJk bh4vtMq3VSQRxHrt1icqAFkulg02Mv6EpRWzKyX4hkOyHzNxrxuLbiXBkJCdPB5V g/WzwsGk9wc= =zbHj -----END PGP SIGNATURE----- --=-=-=--