From: Laurenz Albe <laurenz.albe@cybertec.at>
To: depesz@depesz.com, Jose Osinde <jose.osinde@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Select on partitioned table is very slow
Date: Thu, 25 Aug 2022 12:00:04 +0200
Message-ID: <b3213b4e8bcb84192c3de588fed19effbb3f8543.camel@cybertec.at> (raw)
In-Reply-To: <20220825091014.GA26288@depesz.com>
References: <CACg3g4DmC--d0riLwta8qAnVTdMuA3hxTf5=11dRNYXfOwgx=w@mail.gmail.com>
<20220825091014.GA26288@depesz.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: laurenz.albe@cybertec.at, jose.osinde@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Select on partitioned table is very slow
In-Reply-To: <b3213b4e8bcb84192c3de588fed19effbb3f8543.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox