From: hubert depesz lubaczewski <depesz@depesz.com>
To: 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 11:10:14 +0200
Message-ID: <20220825091014.GA26288@depesz.com> (raw)
In-Reply-To: <CACg3g4DmC--d0riLwta8qAnVTdMuA3hxTf5=11dRNYXfOwgx=w@mail.gmail.com>
References: <CACg3g4DmC--d0riLwta8qAnVTdMuA3hxTf5=11dRNYXfOwgx=w@mail.gmail.com>
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
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: depesz@depesz.com, jose.osinde@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Select on partitioned table is very slow
In-Reply-To: <20220825091014.GA26288@depesz.com>
* 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