Re: Strange query planner behavior

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

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services