Message-ID: <b3213b4e8bcb84192c3de588fed19effbb3f8543.camel@cybertec.at>
Subject: Re: Select on partitioned table is very slow
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: depesz@depesz.com, Jose Osinde <jose.osinde@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Date: Thu, 25 Aug 2022 12:00:04 +0200
In-Reply-To: <20220825091014.GA26288@depesz.com>
References: 
	<CACg3g4DmC--d0riLwta8qAnVTdMuA3hxTf5=11dRNYXfOwgx=w@mail.gmail.com>
	 <20220825091014.GA26288@depesz.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.42.4 (3.42.4-2.fc35) 
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/b3213b4e8bcb84192c3de588fed19effbb3f8543.camel%40cybertec.at>
Precedence: bulk

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