Re: Select on partitioned table is very slow

You are right but the correct query is the one in the attached file. What
we want to do here is to force psql to send the "wrong data types" to
postgres and, as a result of this, get a bad plan.

Cheers,
Jose Osinde

On Thu, Aug 25, 2022 at 3:48 PM Ranier Vilela <ranier(dot)vf(at)gmail(dot)com> wrote:

> Em qui., 25 de ago. de 2022 às 10:43, Jose Osinde <jose(dot)osinde(at)gmail(dot)com>
> escreveu:
>
>>
>> Dear Depesz, Laurenz,
>>
>> Thanks very much for the fast responses. They are actually correct and
>> saved me a lot of time. I couldn't test the cast from the Java test but
>> this is something I can deal with later on (most probably updating the
>> column types to text in the database side instead). But what I could do was
>> reproduce the same problem in the psql console using the cast in the other
>> way. This sentence:
>>
>> explain analyze 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'::citext
>> AND
>> pui.logical_identifier='urn:esa:psa:em16_tgo_frd:data_raw:frd_raw_sc_n_20220729t000000-20220729t235959'::citext;
>>
> The query in explain.txt attached, it seems not the same.
>
> explain analyze 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'::text
> AND pui.logical_identifier='urn:esa:psa:em16_tgo_frd:data_raw:frd_raw_sc_n_20220729t000000-20220729t235959'::text;
>
> ::text?
>
> regards,
>
> Ranier Vilela
>
>