Re: Select on partitioned table is very slow

On Thu, 2022-08-25 at 11:10 +0200, hubert depesz lubaczewski wrote:
> 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.

Quite so.

You are probably using a prepared statement in JDBC.

You probably have to use explicit type casts, like:

select logical_identifier, version_id, lastproduct
from test_product_ui_partition.product_ui pui
where pui.mission_id = ? :: citext
and pui.logical_identifier = ? :: citext

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com