Обсуждение: Strange query planner behavior

Hello

I have a strange problem with the query planner on Postgresql 11.5 on
Debian stretch, the plan differs between the following 2 requests:

- SELECT * FROM LIVRAISON WHERE ID_MASTER = 10 which uses a btree index
on ID_MASTER (the table has 1M rows). Everything is normal
- SELECT * FROM LIVRAISON WHERE ID_MASTER = (SELECT 10) which uses a seq
scan and is 3000 times slower

I don't understand how the planner cannot consider that a subselect with
an = is equivalent to having = VALUE (the subselect either returning 1
row or NULL)

I don't have the same behavior on other column with indexes of the same
table, maybe it's because 99% or the table has ID_MASTER = 0? I can
understand that if the value returned by the subquery is 0 the seqscan
could be faster (in our case it is still slower than index scan but only
by 2 times), but if the subquery does not return 0 in no case the
seqscan could be faster. The question is why is the subquery not
calculated before choosing wether to use the index or not since it will
return a single value?

Thanks for your reply and sorry if the question is stupid

Best regards
Martin Querleu

Hi

so 30. 11. 2019 v 10:31 odesílatel EffiSYS / Martin Querleu <martin.querleu@effisys.fr> napsal:

Hello

I have a strange problem with the query planner on Postgresql 11.5 on
Debian stretch, the plan differs between the following 2 requests:

- SELECT * FROM LIVRAISON WHERE ID_MASTER = 10 which uses a btree index
on ID_MASTER (the table has 1M rows). Everything is normal
- SELECT * FROM LIVRAISON WHERE ID_MASTER = (SELECT 10) which uses a seq
scan and is 3000 times slower

I don't understand how the planner cannot consider that a subselect with
an = is equivalent to having = VALUE (the subselect either returning 1
row or NULL)

I don't have the same behavior on other column with indexes of the same
table, maybe it's because 99% or the table has ID_MASTER = 0? I can
understand that if the value returned by the subquery is 0 the seqscan
could be faster (in our case it is still slower than index scan but only
by 2 times), but if the subquery does not return 0 in no case the
seqscan could be faster. The question is why is the subquery not
calculated before choosing wether to use the index or not since it will
return a single value?

Thanks for your reply and sorry if the question is stupid

please try

1. run vacuum analyze on LIVRAISON

2. send result of EXPLAIN ANALYZE SELECT * FROM ... for both cases

here is a tool for sharing explains https://explain.depesz.com/

Regards

Pavel

Best regards
Martin Querleu

Hi

so 30. 11. 2019 v 10:31 odesílatel EffiSYS / Martin Querleu <martin.querleu@effisys.fr> napsal:

Hello

I have a strange problem with the query planner on Postgresql 11.5 on
Debian stretch, the plan differs between the following 2 requests:

- SELECT * FROM LIVRAISON WHERE ID_MASTER = 10 which uses a btree index
on ID_MASTER (the table has 1M rows). Everything is normal
- SELECT * FROM LIVRAISON WHERE ID_MASTER = (SELECT 10) which uses a seq
scan and is 3000 times slower

I don't understand how the planner cannot consider that a subselect with
an = is equivalent to having = VALUE (the subselect either returning 1
row or NULL)

I don't have the same behavior on other column with indexes of the same
table, maybe it's because 99% or the table has ID_MASTER = 0? I can
understand that if the value returned by the subquery is 0 the seqscan
could be faster (in our case it is still slower than index scan but only
by 2 times), but if the subquery does not return 0 in no case the
seqscan could be faster. The question is why is the subquery not
calculated before choosing wether to use the index or not since it will
return a single value?

Thanks for your reply and sorry if the question is stupid

please try

1. run vacuum analyze on LIVRAISON

2. send result of EXPLAIN ANALYZE SELECT * FROM ... for both cases

here is a tool for sharing explains https://explain.depesz.com/

Regards

Pavel

Best regards
Martin Querleu

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)
cpu_tuple_cost at 0.005 (half default)
cpu_index_tuple_cost at 0.00025 (half defaut)
cpu_operator_cost at 0.00025 (default, by the way I assume we should lower it at 0.0001)

I would expect the seq scan to be more costly than default since both page_cost are higher and cpu_index_tuple_cost lower

I think the main question is whether the query planner is able to pre calculate subqueries with = to use the value returned to get the good query plan

Best regards
Martin

On 30/11/2019 11:00, Pavel Stehule wrote:

so 30. 11. 2019 v 10:55 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:

Hi

so 30. 11. 2019 v 10:31 odesílatel EffiSYS / Martin Querleu <martin.querleu@effisys.fr> napsal:

Hello

I have a strange problem with the query planner on Postgresql 11.5 on
Debian stretch, the plan differs between the following 2 requests:

- SELECT * FROM LIVRAISON WHERE ID_MASTER = 10 which uses a btree index
on ID_MASTER (the table has 1M rows). Everything is normal
- SELECT * FROM LIVRAISON WHERE ID_MASTER = (SELECT 10) which uses a seq
scan and is 3000 times slower

I don't understand how the planner cannot consider that a subselect with
an = is equivalent to having = VALUE (the subselect either returning 1
row or NULL)

I don't have the same behavior on other column with indexes of the same
table, maybe it's because 99% or the table has ID_MASTER = 0? I can
understand that if the value returned by the subquery is 0 the seqscan
could be faster (in our case it is still slower than index scan but only
by 2 times), but if the subquery does not return 0 in no case the
seqscan could be faster. The question is why is the subquery not
calculated before choosing wether to use the index or not since it will
return a single value?

Thanks for your reply and sorry if the question is stupid

please try

1. run vacuum analyze on LIVRAISON

2. send result of EXPLAIN ANALYZE SELECT * FROM ... for both cases

3. do you have some custom settings of planner configuration variables like random_page_cost, seq_page_cost?

here is a tool for sharing explains https://explain.depesz.com/

Regards

Pavel

Best regards
Martin Querleu

-- 
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

so 30. 11. 2019 v 11:29 odesílatel EffiSYS / Martin Querleu <martin.querleu@effisys.fr> napsal:

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)
cpu_tuple_cost at 0.005 (half default)
cpu_index_tuple_cost at 0.00025 (half defaut)
cpu_operator_cost at 0.00025 (default, by the way I assume we should lower it at 0.0001)

I would expect the seq scan to be more costly than default since both page_cost are higher and cpu_index_tuple_cost lower

I think the main question is whether the query planner is able to pre calculate subqueries with = to use the value returned to get the good query plan

The basic problem is in very bad estimation

Seq Scan on livraison  (cost=0.01..2888156.69 rows=1917632 width=697) (actual time=1334.615..1334.615 rows=0 loops=1)

Looks like the estimation lost a const value, and try to estimate result against unknown variable. Probably the table livraison has in id_master some values that has massively higher number than other. Subplans are estimated separately.

There is not simply solution - you have to rewrite your queries - used syntax blocks flattening, and that is wrong.

SELECT * FROM LIVRAISON WHERE ID_MASTER = (SELECT 10)

this query is optimized as two independent queries - SELECT * FROM LIVRAISON WHERE ID_MASTER and SELECT 10. Although "SELECT 10" has const result, first query desn't calculate it. Postgres planner doesn't expect so somebody will write these queries, and don't try to detect const table results. If you rewrite query to

SELECT * FROM LIVRAISON WHERE ID_MASTER IN (SELECT 10)

Then it will be optimized as one query and it should to work.

Pavel

 

Best regards
Martin

On 30/11/2019 11:00, Pavel Stehule wrote:

so 30. 11. 2019 v 10:55 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:

Hi

so 30. 11. 2019 v 10:31 odesílatel EffiSYS / Martin Querleu <martin.querleu@effisys.fr> napsal:

Hello

I have a strange problem with the query planner on Postgresql 11.5 on
Debian stretch, the plan differs between the following 2 requests:

- SELECT * FROM LIVRAISON WHERE ID_MASTER = 10 which uses a btree index
on ID_MASTER (the table has 1M rows). Everything is normal
- SELECT * FROM LIVRAISON WHERE ID_MASTER = (SELECT 10) which uses a seq
scan and is 3000 times slower

I don't understand how the planner cannot consider that a subselect with
an = is equivalent to having = VALUE (the subselect either returning 1
row or NULL)

I don't have the same behavior on other column with indexes of the same
table, maybe it's because 99% or the table has ID_MASTER = 0? I can
understand that if the value returned by the subquery is 0 the seqscan
could be faster (in our case it is still slower than index scan but only
by 2 times), but if the subquery does not return 0 in no case the
seqscan could be faster. The question is why is the subquery not
calculated before choosing wether to use the index or not since it will
return a single value?

Thanks for your reply and sorry if the question is stupid

please try

1. run vacuum analyze on LIVRAISON

2. send result of EXPLAIN ANALYZE SELECT * FROM ... for both cases

3. do you have some custom settings of planner configuration variables like random_page_cost, seq_page_cost?

here is a tool for sharing explains https://explain.depesz.com/

Regards

Pavel

Best regards
Martin Querleu

-- 
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