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 1mCTQS-00035g-Kx for pgsql-performance@arkaria.postgresql.org; Sat, 07 Aug 2021 21:00:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mCTQR-0003bE-GN for pgsql-performance@arkaria.postgresql.org; Sat, 07 Aug 2021 21:00:11 +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 1mCS6h-00072g-6m for pgsql-performance@lists.postgresql.org; Sat, 07 Aug 2021 19:35:43 +0000 Received: from sonic312-24.consmr.mail.ne1.yahoo.com ([66.163.191.205]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1mCS6d-00029L-Rc for pgsql-performance@postgresql.org; Sat, 07 Aug 2021 19:35:42 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1628364936; bh=5QFaJHbPjj+gw5tGslulhI9x1rc6KbEO47zcHONBJsw=; h=Date:From:To:Subject:References:From:Subject:Reply-To; b=dBUKpk66MobMV2SKEGIFa2mKO2oe8WThCd1WUxRj9eDLdAGRYqK0ubfgtoTJW8xTQ6TIqD3NbjF7jRR0YeEL4+6jRYprPk2hBjzRqBvtI7Ux+JjwfMf2HWVKuz42jOWPBDufHzmXscqFYGO7/z9XAsyltaCW9NtaVogC1JUYFRJW93QXG8gDU3QE/ENX8n+/dfYU0zKB0ADiTKkxE93ZAqCPOcmN+KAg5JF3w6BGTksJCVkET4PRqbuiIV4rOvvmQNWPJymlyT+sM90VdA5ecst+jht42cjMW2suvNsAgkb1XsBlwWED8aQW8l5cVL1v3R9qn0qywsvFuDpp5fLkig== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1628364936; bh=EfNOwPjElI+6sNMVr0ZCq8c+gfUK5ADD9cIFhGHE9LX=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=abi8AIxyNaYxwUncqd+yK4nsDi6VV0Ud7nJdHHII+j5xdadVJ/SMhaOWAIR7RWX1IaVlT+MD+Pgv3WoOJit1nT00fGGOsir59zcbrTcUOQCDJ43x0Hg/kZOIB9ycdhxuzLisFLzgDK96+cSX1syADOEeW9sToiLJXvYldHKEhJDX8s+5+WNi4qTtn3FTFM+/FVvv/rwOAfhHIYqU8577wUC1/rPUxq8JzhfOImnDKlTuLnQ1K/CB4Mj1eNFu6j9ubBoP4KZXQOS4/b8O5h83Cmo93vgOvacfh8K/FFxwtrn9PbYpd6cHtCmzAwf4NInNft4ydgSeSGMS4wAO5CUwRA== X-YMail-OSG: jFfaxBsVM1mHKEHJv0fC6VNC0RHUgyV.hLZqEywHuXdPvrrheWhQ4HZ7JOZfPGm T59wi7_PBacwDzhNs1w3jBrDr70Eo0muaiacHdWoEQCqUYFQJ78_.yasjJcSRQPpV3nCyVm26B1e .rHD3f_IsQo3OGE5TawCUa9fL4JofwqQcT8S0GwRjBtkdjsrT9H2pbX9K1rRyP_yphi1IQL8qdWe 1DwDTQNuCFHeFTe4I_6zvNlEFztt.bOgzpepy_Jgehpctr0s_ktHeM52Bq7AmX.RgSpVd9YNHzH1 Ue5U_o5BqyQIT5eaJGE2BtrY39z_B_V6uxVL0sHx7TSe42ExToWs5qAI4VXva7VIIS7IlZRf7ywj .4KBPh9V8xDFcze8t7cczQ8ghbjxC._s82wqVdszivpuzNsETPv8Fq6ZL9NiKtcyFOpU9oGRQT4A bAz.Ue6cZZqhBSFZsIp5IvY4i.gyrt4fex2mvGszIBJ7iDrctRKZtNLrMGuVQg4fFPSqJAaokGFi kgzZC4TO2Lp4VXSS7vbkif8TuV9JcZkxcPLmixv4c6REf_VUI40Ns.h2y6wV4ts5wSobZQ2KoXTI p2bxS_nDBOHNAuksosFZBPNKaesp.2dz8vQETUPRmdwoavMIkciQwUWaSE8JhB0u747gNt9nytl4 ..1vBKYw4WnH_4Pq2y7EzZKmsCa07eO7IWlpR3gfThNGvxptU22ImaOxdxCXq6PiUQ0I79KlyPwD ikmInp8pU.y6Rt2fRSWCoegGALBjXRq3DRDgWFO5.AfUBh8lCe0ALJ3OdvhjfpiqUdnegQdvKtvr QeH8vKz88nptDH7CxF7l_nnVfz9g2VTGEF79F9pzchllRwcWp5HMNP1xtQlSuBN8gISjsSijGzqc MapCNmBhcE.AbMid20MGdT8UKu9tFA4KPzXn2IU7WfSTLDVHAePyj4DjIbQrhwM24sOvS89vWRv5 8PO5WvGzn0NXi5NOqi8FXi2uaEzxSBHUtDLy5YCLssko6WaVRK0RuuB5AWbb4dWFprOj9tZVkPWl taMq506rAh_7gE7n1zeT27OXi.BpaSDhIG_wEstJIdq7GFyKP_HZgvyyRdQ.FC6kGaVGp7GIMGNh GnBzpo9YSFktk7oTyEVf_zs5W7XcTf3onUK8s89zUkUikDPyRqvAgbaPRSITJAmA77eR9aq7VRmz ltvyw4Po47WUcsohB9sW4FA3GJYXXJtwMZJFwTu9rRz8dqybeQmvvQMMLJdkLbpCdz35FesciUhg P0y0y91RjAFgLO0YTKXHN2m74hWv26PdJK1LW8cl42isYZO05SYDsITsC3ILxW._w5J1.XK2x0Z3 9iKmKznSApqj7OvAhXbcT2O7IHUqhlSwpZ_atZjH3Mc_NWwf3YtpVKGHCiAhDMl0tpP2KM3Y.P1k wYTEr50J3aSOTa84WeCeLnF1b9N8ya7LeUQEJ75MhS1YTdZUduVGH0NYKOu.BFBNuH8NQw9iehRj fYtlzNqOK4odsd5ZybgKS6V3Ce9RiXhiiOJsgdu3MEYgFQjm7NsT8dSre3dV8oBht3LINcmoaILb .OAg7.47_pw_ZIGJuyoEaXRtHzwCLNW9ypLu.Qv01FxlRVHooRUgj4uLu83xC3CaG3qHGVAMmrjC czKzw4nshOxAgM_MdWufCEIHDm9IKhn6KcnVOV.8jomG44iyWXcXeP1JkvO4kr6zb0iw8pAoo84h o0b.j3M7Kd4khYJxD7dyu2RlMP4T2mO87IAc17MYHvpknbH63dxpuYcOuIw_6.WpGUm75omUeFix yNWBe0u5CB39SA8EEQihSgFul4WcW3i_sGZZ_HfaPqxhdYeEQpfamteVuqEqz8Wt2GB9s2tr55O5 KVfpd50kWO76J0gx2EnppjfjKSJn8hn7xB99N0Kv7X0dnSBHO589PB5cod6.vtI397G9o7ZFzVnF YqezAvslNZrxTvG.uiqWIVylFDQnmU1v5T6q4pxSVmfUY3idd9lNgY_MQHnuC9ce4mdnFfyxepfB PVwBOVPoxhc4B4aZHyFwN5yG_lfFCf8e3DuanYIPyeleHf9.yoKY98G5SgsQdIbSED11P8qE7RAJ QDEvIh3QgVf1BfYg8ZMimFEDr3NQeiG_xFoojxBWCN6.i5RTCbxrXPag7FclSOen_ZeIDyxgHIUO E6Skl6j6QaT_pxYoxZr663z0ffFJ_QbsDLtDGR7K9.QXlwUWFNWvOU8CveNsPEDZqQY8N7I9M6Pa LYiP80bgeTSWHniWk5TxlocD_jFZ..eVIbGclcB1TNQJMInppiqrwJJ.8K3pbhC9EoAluqSyBhtG lUTVg2snCfMxJYSz37zuMLjtDKoVqCmZxzZ9EvWlBZ_9gIh8I.iI2Jb.aZ5H4Wu8Kk5JiytD7rUf NkkT_9CGZ2kSOvNYZKub_3LXy6A-- X-Sonic-MF: Received: from sonic.gate.mail.ne1.yahoo.com by sonic312.consmr.mail.ne1.yahoo.com with HTTP; Sat, 7 Aug 2021 19:35:36 +0000 Date: Sat, 7 Aug 2021 19:35:25 +0000 (UTC) From: Alex To: "pgsql-performance@postgresql.org" Message-ID: <1557921338.669347.1628364925994@mail.yahoo.com> Subject: Slow query because lexeme index not used MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_669344_395611334.1628364925993" References: <1557921338.669347.1628364925994.ref@mail.yahoo.com> X-Mailer: WebService/1.1.18796 YMailNorrin Content-Length: 9351 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_669344_395611334.1628364925993 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Table "product" has a GIN index on "lexeme" column (tsvector) that is not u= sed. Query that doesn't use lexeme idx:=C2=A0=C2=A0https://explain.dalibo.com/pl= an/BlB#plan, ~8s, ~60.000 blocks needed Query forced to use lexeme idx: https://explain.dalibo.com/plan/i52, ~800ms= (10x less), ~15.000 blocks needed (x4 less) Table metdata: =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 relname=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | relpages | reltuples | relallvisible= | relkind | relnatts | relhassubclass | reloptions | pg_table_size --------------------------+----------+-----------+---------------+---------= +----------+----------------+------------+--------------- =C2=A0product_property_default |=C2=A0=C2=A0=C2=A0=C2=A0 8992 |=C2=A0=C2=A0= =C2=A0 622969 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 8992 = | r=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 16 | f=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 73719808 =C2=A0product=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0 49686 |=C2= =A0=C2=A0=C2=A0 413840 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 49= 686 | r=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 14 | f=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0 493314048 Table stats: =C2=A0=C2=A0 frac_mcv=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 tablename=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | attna= me | inherited | null_frac | n_distinct=C2=A0 | n_mcv | n_hist | correlatio= n ---------------+--------------------------+---------+-----------+----------= -+-------------+-------+--------+------------- =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 | product=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | lexeme=C2=A0 | f=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0 0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 -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 0.99773335 | product_property_default | meaning | f=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 63 |=C2=A0=C2=A0=C2=A0 39 |=C2=A0=C2=A0=C2=A0=C2=A0 24 |=C2=A0 0.194= 44875 =C2=A0=C2=A0=C2=A0=C2=A0 0.6416333 | product_property_default | first=C2=A0= =C2=A0 | f=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 2193 |=C2=A0=C2=A0 100 |=C2=A0=C2=A0=C2=A0 101 | -0.09763639 =C2=A00.00023333334 | product_property_default | product | f=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 0 | -0.15221785 |=C2=A0=C2=A0=C2=A0=C2=A0 1 |=C2=A0=C2=A0=C2=A0 1= 01 |=C2=A0 0.08643274 Using windows docker with wsl2.Both cases are run with cold cache.All datab= ase memory is limited to 1GB by using .wslconfig file with memory=3D1GB, al= so the docker container is limited to 1GB.=20 My requirement is to optimize disk access with this limited memory Postgres 12.4 ------=_Part_669344_395611334.1628364925993 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Table "product" has a GIN index on "lexeme" = column (tsvector) that is not used.

Query that does= n't use lexeme idx:  https://explain.dali= bo.com/plan/BlB#plan, ~8s, ~60.000 blocks needed

= Query forced to use lexeme idx: https://explain.dalibo.c= om/plan/i52, ~800ms (10x less), ~15.000 blocks needed (x4 les= s)

Table metdata:

  =        relname     &= nbsp;    | relpages | reltuples | relallvisible | relkind | = relnatts | relhassubclass | reloptions | pg_table_size
-----------------= ---------+----------+-----------+---------------+---------+----------+-----= -----------+------------+---------------
 product_property_default = |     8992 |    622969 |  &nbs= p;       8992 | r    &nbs= p;  |       16 | f   &nbs= p;          |   = ;         |    =   73719808
 product      &n= bsp;           | &nb= sp;  49686 |    413840 |     &= nbsp;   49686 | r       | &nbs= p;     14 | f       =        |      &= nbsp;     |     493314048

Table stats:

   frac_mcv    |    = ;    tablename       &nbs= p; | attname | inherited | null_frac | n_distinct  | n_mcv | n_hist | = correlation
---------------+--------------------------+---------+-------= ----+-----------+-------------+-------+--------+-------------
 &nbs= p;             = | product           =        | lexeme  | f   &n= bsp;     |       &nb= sp; 0 |          -1 | &nb= sp;     |        |    0.99773335 | product_property_default | meaning | f&nb= sp;        |    &nbs= p;    0 |        &nb= sp; 63 |    39 |     24 |  0.194448= 75
     0.6416333 | product_property_default | first=    | f         | &nb= sp;       0 |     &n= bsp;  2193 |   100 |    101 | -0.09763639
=  0.00023333334 | product_property_default | product | f  &nb= sp;      |      &nbs= p;  0 | -0.15221785 |     1 |    10= 1 |  0.08643274


Using win= dows docker with wsl2. Both cases are run with cold cache.
All database memory is limited to 1GB by using .wslconfig file with memory=3D1GB, also the docker co= ntainer is limited to 1GB.
My requirement is to optimize disk access with t= his limited memory


Postgres 12.4



<= /div>


<= br>


------=_Part_669344_395611334.1628364925993--