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 1ibiIZ-0004bW-3z for pgsql-bugs@arkaria.postgresql.org; Mon, 02 Dec 2019 09:47:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ibiIX-0003hr-Gs for pgsql-bugs@arkaria.postgresql.org; Mon, 02 Dec 2019 09:47:17 +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 1ibhsd-0001gu-TO for pgsql-bugs@lists.postgresql.org; Mon, 02 Dec 2019 09:20:31 +0000 Received: from smtp02.srv.celeste.fr ([185.5.111.9]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ibhsb-0006yD-9Q for pgsql-bugs@lists.postgresql.org; Mon, 02 Dec 2019 09:20:31 +0000 Received: from smtp02.srv.celeste.fr (localhost [127.0.0.1]) by smtp02.srv.celeste.fr (Postfix) with ESMTP id 3BBF743EC1; Mon, 2 Dec 2019 10:20:27 +0100 (CET) Received: from [192.168.1.107] (unknown [178.16.171.131]) by smtp02.srv.celeste.fr (Postfix) with ESMTP id 201D63F970; Mon, 2 Dec 2019 10:20:27 +0100 (CET) Reply-To: martin.querleu@effisys.fr Subject: Re: Strange query planner behavior To: Tomas Vondra Cc: Pavel Stehule , PostgreSQL mailing lists References: <798855a5-4abe-b1d0-68ab-f21966d2c3a1@effisys.fr> <20191130145352.hhr4n45cdsyjkzr5@development> From: EffiSYS / Martin Querleu Organization: EffiSYS Message-ID: <4d3aeef2-85c1-e9e2-d4d8-396a0358fbe2@effisys.fr> Date: Mon, 2 Dec 2019 10:20:30 +0100 User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:60.0) Gecko/20100101 Thunderbird/60.9.1 MIME-Version: 1.0 In-Reply-To: <20191130145352.hhr4n45cdsyjkzr5@development> Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Content-Language: en-US X-Virus-Scanned: ClamAV using ClamSMTP List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Hi Tomas I tried the method of using IN instead of =, it's not really better in the present case: EFT_MBON=# explain analyse select * from livraison where id_master in (select 10);                                                           QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------  Nested Loop Semi Join  (cost=0.00..211841.54 rows=1919474 width=697) (actual time=1245.271..1245.271 rows=0 loops=1)    Join Filter: (livraison.id_master = (10))    Rows Removed by Join Filter: 1921796    ->  Seq Scan on livraison  (cost=0.00..201476.37 rows=1919474 width=697) (actual time=0.006..211.196 rows=1921796 loops=1)    ->  Materialize  (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1921796)          ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)  Planning Time: 0.200 ms  Execution Time: 1245.309 ms (8 lignes) I tried many different query planner cost variables (including defaults) but no impact, as expected (default for random_page_cost is 4 so 15 is about 3-4 times the default) This was a very precise case of a special data distribution, so we'll go for a workaround on this one Best regards and thanks Martin On 30/11/2019 15:53, Tomas Vondra wrote: > On Sat, Nov 30, 2019 at 11:29:53AM +0100, EffiSYS / Martin Querleu wrote: >> Hi Pavel >> >> Thanks for the fast reply >> Our databases are VACUUMed everyday. I did it again but no difference >> >> Here are the query plans: >> >> EFT_MBON=# explain analyse select * from livraison where id_master = 10; >>  QUERY PLAN >> -------------------------------------------------------------------------------------------------------------------------------------------- >> >>  Index Scan using pour_recherche_sous_livraison on livraison >>  (cost=0.03..15.04 rows=1 width=697) (actual time=0.017..0.017 rows=0 >> loops=1) >>    Index Cond: (id_master = 10) >>  Planning Time: 0.124 ms >>  Execution Time: 0.036 ms >> (4 lignes) >> >> EFT_MBON=# explain analyse select * from livraison where id_master = >> (select 10); >>                                                       QUERY PLAN >> ----------------------------------------------------------------------------------------------------------------------- >> >>  Seq Scan on livraison  (cost=0.01..2888156.69 rows=1917632 >> width=697) (actual time=1334.615..1334.615 rows=0 loops=1) >>    Filter: (id_master = $0) >>    Rows Removed by Filter: 1918196 >>    InitPlan 1 (returns $0) >>      ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual >> time=0.000..0.001 rows=1 loops=1) >>  Planning Time: 0.138 ms >>  Execution Time: 1334.642 ms >> (7 lignes) >> >> Regarding the cost calculator the configuration is as follows: >> >> random_page_cost and seq_page_cost are identical since the data is >> 100% in RAM (both at 15.0, 3 times default) > > Ummm, what? Does this mean you have > random_page_cost = 15 > seq_page_cost = 15 > > Neither of that is 3 times the default value, though, so maybe I just > don't understand correctly. > > regards > -- Martin Querleu - Directeur Général EffiSYS (www.effitrace.fr - www.logistique-e-commerce.fr) martin.querleu@effisys.fr 3, rue Gustave Delory 59000 Lille Tél: +33 9 54 28 38 76 Vous rencontrez un problème d'utilisation sur effitr@ce? =====> écrivez à support@effisys.fr Vous rencontrez un problème technique au niveau des échanges de données? =====> écrivez à supervision@effisys.fr