Re: Getting an index scan to be a parallel index scan

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

On Wed, Feb 1, 2023 at 3:30 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Wed, 1 Feb 2023 at 18:39, Alex Kaiser <alextkaiser(at)gmail(dot)com> 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
>