- Notifications
You must be signed in to change notification settings - Fork67
Closed
Labels
Description
Problem description
We have a table calledcustomers, and it has about 4K partition.
For some of the newer partitions,EXPLAIN UPDATE customers SET ... WHERE ...
results in Seq Scan on the parent table, making it significantly slower.
# EXPLAIN UPDATE customers SET ... WHERE account_id = 1234 and customer_uuid = ...; QUERY PLAN--------------------------------------------------------------------------------------------------------------- Update on customers (cost=0.00..2.29 rows=2 width=369) Update on customers Update on customers_4813 -> Seq Scan on customers (cost=0.00..0.00 rows=1 width=371) Filter: ((account_id = 1234) AND (customer_uuid = '....'::uuid)) -> Index Scan using customers_4813_customer_uuid_key on customers_4813 (cost=0.28..2.29 rows=1 width=367) Index Cond: (customer_uuid = '....'::uuid) Filter: (account_id = 1234)(8 rows)Time: 31561.109 msFor other cases, it produces the correct query plan
# EXPLAIN UPDATE customers SET ... WHERE account_id = 1 and customer_uuid = '....'; QUERY PLAN---------------------------------------------------------------------------------------------------------- Update on customers_5 (cost=0.29..2.31 rows=1 width=1371) -> Index Scan using customers_5_customer_uuid_key on customers_5 (cost=0.29..2.31 rows=1 width=1371) Index Cond: (customer_uuid = '....'::uuid) Filter: (account_id = 1)(4 rows)Time: 0.721 msExpected Result
No matter which partition it is, it should produce an optimized query plan.
Environment
select * from pathman_config_params; partrel | enable_parent | auto | init_callback | spawn_using_bgw--------------------------+---------------+------+---------------+----------------- customers | f | t | | f extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition--------------------+----------+--------------+----------------+------------+---------------+-------------- plpgsql | 10 | 11 | f | 1.0 | | btree_gist | 10 | 2200 | t | 1.2 | | pg_stat_statements | 10 | 2200 | t | 1.4 | | pg_pathman | 10 | 2200 | f | 1.4 | {16966,16977} | {"",""} version-------------------------------------------------------------------------------------------------------- PostgreSQL 9.6.4 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.3) 4.8.4, 64-bit(1 row)get_pathman_lib_version------------------------- 10402(1 row)