Rainier,
I tried using the any syntax (had to modify your query slightly) and it didn't result in any change in the query plan.
postgres=# explain select * from testing where id = ANY(array[1608377,5449811, ... <removed for brevity> ... ,4654284,3558460]::integer[]);
QUERY PLAN
----------------------------------------------------------------------------------
Gather (cost=0.43..6138.81 rows=1000 width=74)
Workers Planned: 1
Single Copy: true
-> Index Scan using testing_pkey on testing (cost=0.43..6138.81 rows=1000 width=74)
Index Cond: (id = ANY ('{1608377,5449811, ... <removed for brevity> ... ,4654284,3558460}'::integer[]))
I've never messed around with extended statistics, but I'm not sure how they would help here. From what I've read they seem to help when your query is restricting over multiple columns. Since this query is only on one column I'm not sure what a good "CREATE STATISTICS ..." command to run would be to improve the query plan. Any suggestions?
David,
As for how I found 'force_parallel_mode', I think I found it first here: https://postgrespro.com/list/thread-id/2574997 and then I also saw it when I was searching for 'parallel' on https://postgresqlco.nf .
It's not that I think the parameter would help my query, it was really as a last resort to try and force the query to be parallel. Without that parameter, it just does a normal index scan (see the result below). My thinking with using that parameter was to see if I could force a parallel query plan just to see if maybe the planner just thought the parallel plan would be more expensive. So I was surprised to see that even with that parameter turned on it doesn't actually do anything in parallel. Here is the plan with that parameter turned off:
postgres=# set force_parallel_mode = off;
SET
postgres=# explain select * from testing where id = ANY(array[1608377,5449811, ... <removed for brevity> ... ,4654284,3558460]::integer[]);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Index Scan using testing_pkey on testing (cost=0.43..6138.81 rows=1000 width=74)
Index Cond: (id = ANY ('{1608377,5449811, ... < removed for brevity > ... 4654284,3558460}'::integer[]))
(2 rows)
Thanks,
Alex Kaiser
I tried using the any syntax (had to modify your query slightly) and it didn't result in any change in the query plan.
postgres=# explain select * from testing where id = ANY(array[1608377,5449811, ... <removed for brevity> ... ,4654284,3558460]::integer[]);
QUERY PLAN
----------------------------------------------------------------------------------
Gather (cost=0.43..6138.81 rows=1000 width=74)
Workers Planned: 1
Single Copy: true
-> Index Scan using testing_pkey on testing (cost=0.43..6138.81 rows=1000 width=74)
Index Cond: (id = ANY ('{1608377,5449811, ... <removed for brevity> ... ,4654284,3558460}'::integer[]))
I've never messed around with extended statistics, but I'm not sure how they would help here. From what I've read they seem to help when your query is restricting over multiple columns. Since this query is only on one column I'm not sure what a good "CREATE STATISTICS ..." command to run would be to improve the query plan. Any suggestions?
David,
As for how I found 'force_parallel_mode', I think I found it first here: https://postgrespro.com/list/thread-id/2574997 and then I also saw it when I was searching for 'parallel' on https://postgresqlco.nf .
It's not that I think the parameter would help my query, it was really as a last resort to try and force the query to be parallel. Without that parameter, it just does a normal index scan (see the result below). My thinking with using that parameter was to see if I could force a parallel query plan just to see if maybe the planner just thought the parallel plan would be more expensive. So I was surprised to see that even with that parameter turned on it doesn't actually do anything in parallel. Here is the plan with that parameter turned off:
postgres=# set force_parallel_mode = off;
SET
postgres=# explain select * from testing where id = ANY(array[1608377,5449811, ... <removed for brevity> ... ,4654284,3558460]::integer[]);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Index Scan using testing_pkey on testing (cost=0.43..6138.81 rows=1000 width=74)
Index Cond: (id = ANY ('{1608377,5449811, ... < removed for brevity > ... 4654284,3558460}'::integer[]))
(2 rows)
Thanks,
Alex Kaiser
On Wed, Feb 1, 2023 at 3:30 AM David Rowley <dgrowleyml@xxxxxxxxx> wrote:
On Wed, 1 Feb 2023 at 18:39, Alex Kaiser <alextkaiser@xxxxxxxxx> wrote:
> postgres=# set force_parallel_mode = on;
There's been a bit of debate about that GUC and I'm wondering how you
came to the conclusion that it might help you. Can you share details
of how you found out about it and what made you choose to set it to
"on"?
David
