samimseih
commented
8 months ago Thanks for the report @michaelpq
After we spoke offline, I spent time looking further into this. What I observed is the follows:
-
skipping over setup_scan_method_enforcement https://github.com/ossc-db/pg_hint_plan/blob/master/pg_hint_plan.c#L3955-L3956 does not prevent the setting up of parallel enforcement https://github.com/ossc-db/pg_hint_plan/blob/master/pg_hint_plan.c#L3986 I confirmed with some additional debugging statements.
-
Also, Testing the repro case on 1.4.1, I discovered some very inconsistent/odd parallel enforcement behavior that 684986a exposes in 1.4.2, but I do not think is the cause of. See the cases below.
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------+---------+------------+------------------------------
pg_hint_plan | 1.4.1 | hint_plan |
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
set parallel_setup_cost=0;
set parallel_tuple_cost=0;
set min_parallel_index_scan_size to 0;
set min_parallel_table_scan_size=0;
set max_parallel_workers_per_gather To 4;I have a repro of the test case mentioned in the thread.
postgres=# /*+
IndexScan(a bookings_total_amount_idx)
IndexScan(b bookings_tmp_not_exist)
Parallel(a 4 hard)
Parallel(b 2 hard)
*/
EXPLAIN(costs false) SELECT * FROM bookings a,bookings_tmp b
WHERE a.book_ref = b.book_ref and a.total_amount > 28000.00;
QUERY PLAN
-------------------------------------------------------------------------------
Nested Loop
Join Filter: ((a.book_ref)::text = (b.book_ref)::text)
-> Gather
Workers Planned: 4
-> Parallel Index Scan using bookings_total_amount_idx on bookings a
Index Cond: (total_amount > 28000.00)
-> Gather
Workers Planned: 2
-> Parallel Seq Scan on bookings_tmp b
(9 rows)Here is where things get interesting. If only the Parallel hints are supplied, no parallel hint is enforced.
postgres=# /*+
Parallel(a 4 hard)
Parallel(b 2 hard)
*/
EXPLAIN(costs false) SELECT * FROM bookings a,bookings_tmp b
WHERE a.book_ref = b.book_ref and a.total_amount > 28000.00;
QUERY PLAN
----------------------------------------------------------
Nested Loop
Join Filter: ((a.book_ref)::text = (b.book_ref)::text)
-> Seq Scan on bookings a
Filter: (total_amount > 28000.00)
-> Seq Scan on bookings_tmp b
(5 rows)I can force the parallel plan by keeping only index scans enabled. But, notice it's not a Parallel Seq scan on "b", but rather a Parallel Index Scan.
set enable_mergejoin = off;
set enable_seqscan = off;
set enable_hashjoin = off;
SET
SET
SET
postgres=# /*+
Parallel(a 6 hard)
Parallel(b 2 hard)
*/
EXPLAIN(costs false) SELECT * FROM bookings a,bookings_tmp b
WHERE a.book_ref = b.book_ref and a.total_amount > 28000.00;
QUERY PLAN
---------------------------------------------------------------------------
Nested Loop
Join Filter: ((a.book_ref)::text = (b.book_ref)::text)
-> Gather
Workers Planned: 6
-> Parallel Index Scan using bookings_pkey on bookings a
Filter: (total_amount > 28000.00)
-> Gather
Workers Planned: 2
-> Parallel Index Scan using bookings_tmp_pkey on bookings_tmp b
(9 rows)It is clear that parallel enforcement, including the "hard" option, is not showing the expected results even in 1.4.1
thoughts?
Regards,
Sami
michaelpq
A user has reported a regression in the latest release series regarding subject, related to commit 684986acc3b3156aaf22c1571cd44dd34059a346 (applies to all stable branches). Imagine two simple tables:
Now here is an exotic case:
Under 1.4.1, we are getting this plan:
I think that this is not correct, for two reasons:
Now, under 1.4.2, we get this plan:
This is better in the fact that IndexScan(b bookings_tmp_not_exist) is marked as unused. Still, this is worse in the fact that the Parallel hint with the number of workers gets entirely ignored, leading to a regression.
I've been considering a couple of options about what to tweak in the case where an index restriction is applied, but my mind comes down to a few conclusions: