Hi, On Thu, Aug 25, 2022 at 10:49:51AM +0200, Jose Osinde wrote: > select logical_identifier, version_id, lastproduct > from test_product_ui_partition.product_ui pui > where pui.mission_id='urn:esa:psa:context:investigation:mission.em16' > and pui.logical_identifier='urn:esa:psa:em16_tgo_frd:data_raw:frd_raw_sc_n_20220729t000000-20220729t235959' > EXPLAIN ANALYZE FROM PGADMIN > > Index Scan using product_ui_em16_logical_identifier_idx on > product_ui_em16 pui (cost=0.69..19.75 rows=7 width=112) (actual > time=0.133..0.134 rows=1 loops=1) > [...] Index Cond: (logical_identifier = > 'urn:esa:psa:em16_tgo_frd:data_raw:frd_raw_sc_n_20220729t000000-20220729t235959'::citext)" > [...] Filter: (mission_id = > 'urn:esa:psa:context:investigation:mission.em16'::citext)" > Planning Time: 0.237 ms > Execution Time: 0.149 ms I really wish you didn't butcher explains like this, but we can work with it. Please note that the condition for filter is: mission_id = 'urn:esa:psa:context:investigation:mission.em16'::citext Specifically, column mission_id (which is partition key) is compared with some value that is in citext type - same as column. This means that pg can take this value, compare with partitioning schema, and pick one partition. Now look at the explain from java: > Filter: (((mission_id)::text = > 'urn:esa:psa:context:investigation:mission.em16'::text) AND The rest is irrelevant. The important part is that java sent query that doesn't compare value of column mission_id with some value, but rather compares *cast* of the column. Since it's not column value, then partitioning can't check what's going on (cast can just as well make it totally different value), and it also can't really use index on mission_id. Why it happens - no idea, sorry, I don't grok java. But you should be able to test/work on fix with simple, non-partitioned table, just make there citext column, and try searching for value in it, and check explain from the search. If it will cast column - it's no good. Sorry I can't tell you what to fix, but perhaps this will be enough for you to find solution. Best regards, depesz
