CODEWITHSUNDEEP
sqldatabasepostgresqlamazon-aurora
HelenA
HelenA

Reputation: 96

Aurora Postgresql partition by list not used

I have this situation where in the Aurora PostgreSQL database I have two tables:

CREATE TABLE tableA (
    a_index text NOT NULL,
    a_description text NOT NULL,
    a_client text NULL,
    CONSTRAINT a_pk PRIMARY KEY (a_index)
);
CREATE INDEX a_index_pk_idx ON tableA USING hash (a_index);

CREATE TABLE tableB (
    b_id serial4 NOT NULL,
    b_a_index text NOT NULL,
    b_stindex text NOT NULL,
    b_status text NOT NULL,
    CONSTRAINT b_pk PRIMARY KEY (b_id, b_a_index)
)
PARTITION BY LIST (b_a_index);

ALTER TABLE tableB ADD CONSTRAINT b_a_fk FOREIGN KEY (b_a_index) REFERENCES tableA(a_index) MATCH FULL;

When I try to make a simple join between the two tables, in the execution plan I notice that all partitions from the tableB are scanned (Parallel Seq Scan)

SELECT * 
FROM tableA
INNER JOIN tableB
ON b_a_index = a_index
WHERE a_client='ABC';

May someone tell me why not only the corresponding partition is scanned, as I was expecting? Thank you!

L.E: The query plan:

Gather  (cost=1016.54..1263414.50 rows=402743 width=176) (actual time=9108.282..27685.157 rows=9670372 loops=1)
  Output: tableB.b_id, tableB.b_a_index, tableB.b_stindex, tableB.b_status, tableA.a_index, tableA.a_description, tableA.a_client
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=19716 read=688292
  I/O Timings: read=19374676.659
  ->  Hash Join  (cost=16.54..1222140.20 rows=167810 width=176) (actual time=17745.407..23661.627 rows=3223457 loops=3)
        Output: tableB.b_id, tableB.b_a_index, tableB.b_stindex, tableB.b_status, tableA.a_index, tableA.a_description, tableA.a_client
        Inner Unique: true
        Hash Cond: (tableB.b_a_index = tableA.a_index)
        Buffers: shared hit=19716 read=688292
        I/O Timings: read=19374676.659
        Worker 0:  actual time=28921.541..28921.545 rows=0 loops=1
          Buffers: shared hit=7748 read=311076
          I/O Timings: read=6964320.875
        Worker 1:  actual time=15206.799..17833.363 rows=1421435 loops=1
          Buffers: shared hit=4540 read=155998
          I/O Timings: read=5568948.894
        ->  Parallel Append  (cost=0.00..1145148.10 rows=29086961 width=48) (actual time=1.798..15132.937 rows=23276854 loops=3)
              Buffers: shared hit=19585 read=688292
              I/O Timings: read=19374676.659
              Worker 0:  actual time=0.978..19849.500 rows=31198966 loops=1
                Buffers: shared hit=7683 read=311076
                I/O Timings: read=6964320.875
              Worker 1:  actual time=4.408..11818.087 rows=16000668 loops=1
                Buffers: shared hit=4475 read=155998
                I/O Timings: read=5568948.894
              ->  Parallel Seq Scan on tableB_p1 tableB_5  (cost=0.00..548244.96 rows=15856596 width=48) (actual time=1.070..7624.836 rows=19030874 loops=2)
                    Output: tableB_5.b_id, tableB_5.b_a_index, tableB_5.b_stindex, tableB_5.b_status
                    Buffers: shared hit=7683 read=381484
                    I/O Timings: read=8189818.169
                    Worker 0:  actual time=0.977..13108.626 rows=31198966 loops=1
                      Buffers: shared hit=7683 read=311076
                      I/O Timings: read=6964320.875
              ->  Parallel Seq Scan on tableB_p2 tableB_1  (cost=0.00..206850.64 rows=6072264 width=48) (actual time=4.406..7034.632 rows=14579233 loops=1)
                    Output: tableB_1.b_id, tableB_1.b_a_index, tableB_1.b_stindex, tableB_1.b_status
                    Buffers: shared hit=2933 read=143195
                    I/O Timings: read=3630231.053
                    Worker 1:  actual time=4.406..7034.632 rows=14579233 loops=1
                      Buffers: shared hit=2933 read=143195
                      I/O Timings: read=3630231.053
              ->  Parallel Seq Scan on tableB_p3 tableB_3  (cost=0.00..137807.15 rows=4025415 width=48) (actual time=2.213..1730.014 rows=4835186 loops=2)
                    Output: tableB_3.b_id, tableB_3.b_a_index, tableB_3.b_stindex, tableB_3.b_status
                    Buffers: shared hit=7930 read=89359
                    I/O Timings: read=5369919.724
                    Worker 1:  actual time=0.969..735.713 rows=1421435 loops=1
                      Buffers: shared hit=1542 read=12803
                      I/O Timings: read=1938717.840
              ->  Parallel Seq Scan on tableB_p4 tableB_4  (cost=0.00..105144.03 rows=3088803 width=48) (actual time=2.136..3916.330 rows=7414429 loops=1)
                    Output: tableB_4.b_id, tableB_4.b_a_index, tableB_4.b_stindex, tableB_4.b_status
                    Buffers: shared hit=3 read=74253
                    I/O Timings: read=2184699.048
              ->  Parallel Seq Scan on tableB_p5 tableB_6  (cost=0.00..1653.41 rows=61641 width=47) (actual time=0.007..11.395 rows=104780 loops=1)
                    Output: tableB_6.b_id, tableB_6.b_a_index, tableB_6.b_stindex, tableB_6.b_status
                    Buffers: shared hit=1036 read=1
                    I/O Timings: read=8.664
              ->  Parallel Seq Scan on tableB_p6 tableB_2  (cost=0.00..13.12 rows=312 width=124) (actual time=0.002..0.002 rows=0 loops=1)
                    Output: tableB_2.b_id, tableB_2.b_a_index, tableB_2.b_stindex, tableB_2.b_status
        ->  Hash  (cost=16.50..16.50 rows=3 width=128) (actual time=0.019..0.020 rows=1 loops=3)
              Output: tableA.a_index, tableA.a_description, tableA.a_client
              Buckets: 1024  Batches: 1  Memory Usage: 9kB
              Buffers: shared hit=3
              Worker 0:  actual time=0.021..0.022 rows=1 loops=1
                Buffers: shared hit=1
              Worker 1:  actual time=0.024..0.024 rows=1 loops=1
                Buffers: shared hit=1
              ->  Seq Scan on tableA  (cost=0.00..16.50 rows=3 width=128) (actual time=0.011..0.013 rows=1 loops=3)
                    Output: tableA.a_index, tableA.a_description, tableA.a_client
                    Filter: (tableA.a_client = 'ABC'::text)
                    Rows Removed by Filter: 4
                    Buffers: shared hit=3
                    Worker 0:  actual time=0.012..0.013 rows=1 loops=1
                      Buffers: shared hit=1
                    Worker 1:  actual time=0.014..0.015 rows=1 loops=1
                      Buffers: shared hit=1
Planning:
  Buffers: shared read=5
  I/O Timings: read=4.690
Planning Time: 5.357 ms
Execution Time: 28976.177 ms

Upvotes: 0

Views: 212

Answers (1)

jbgorski
jbgorski

Reputation: 1939

It seems that the partition pruning doesn't work with hash and merge join. For some reason , the query optimizer chooses hash join for your query. If you want to use partition pruning function you need to force the query optimizer to use nested loop join. For this purpose you can use Aurora PostgreSQL query plan management or tune Server Configuration - Query Planning. I think the first option is better than the second.

The key question is whether using of partition pruning and nested loop is the best solution in your case, you need to check that. When you force nested loop join the query plan looks like below (with the mention: "(never executed)").

"Nested Loop  (cost=0.42..491542.61 rows=59 width=148) (actual time=0.047..1863.185 rows=6000000 loops=1)"
"  ->  Index Scan using a_client_a_index_idx on tablea  (cost=0.42..8.44 rows=1 width=99) (actual time=0.034..0.043 rows=2 loops=1)"
"        Index Cond: (a_client = 'ABC'::text)"
"  ->  Append  (cost=0.00..361534.15 rows=13000003 width=49) (actual time=0.012..592.062 rows=3000000 loops=2)"
"        ->  Seq Scan on tableb_1  (cost=0.00..68428.00 rows=3000000 width=49) (actual time=0.006..452.645 rows=3000000 loops=1)"
"              Filter: (tablea.a_index = b_a_index)"
"        ->  Seq Scan on tableb_2  (cost=0.00..45619.00 rows=2000000 width=49) (never executed)"
"              Filter: (tablea.a_index = b_a_index)"
"        ->  Seq Scan on tableb_3  (cost=0.00..22810.00 rows=1000000 width=49) (never executed)"
"              Filter: (tablea.a_index = b_a_index)"
"        ->  Seq Scan on tableb_4  (cost=0.00..22810.00 rows=1000000 width=49) (never executed)"
"              Filter: (tablea.a_index = b_a_index)"
"        ->  Seq Scan on tableb_5  (cost=0.00..68428.00 rows=3000000 width=49) (never executed)"
"              Filter: (tablea.a_index = b_a_index)"
"        ->  Seq Scan on tableb_6  (cost=0.00..68428.00 rows=3000000 width=49) (actual time=0.007..351.330 rows=3000000 loops=1)"
"              Filter: (tablea.a_index = b_a_index)"
"        ->  Bitmap Heap Scan on tableb_7  (cost=4.02..11.13 rows=3 width=100) (never executed)"
"              Recheck Cond: (b_a_index = tablea.a_index)"
"              ->  Bitmap Index Scan on tableb_7_b_a_index_idx  (cost=0.00..4.02 rows=3 width=0) (never executed)"
"                    Index Cond: (b_a_index = tablea.a_index)"
"Planning Time: 0.372 ms"
"Execution Time: 2070.149 ms"

Upvotes: 0

Related Questions