Re: Select on partitioned table is very slow

From: Jose Osinde <jose(dot)osinde(at)gmail(dot)com>
To: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
Cc: depesz(at)depesz(dot)com, pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Re: Select on partitioned table is very slow
Date: 2022-08-25 13:42:46
Message-ID: CACg3g4DxhLdgQR2E-cHvo+-CBq3bLqJkxmCJ3_puFg7GWLbaPQ@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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;

Creates an output equivalent to that returned from the JAVA application and
reproduces the exact same problems: Scans all the partitions instead of
select the right one and uses sec scans for all the cases.
Attached the result.

Again, many thanks for your help,
Jose Osinde

Attachment Content-Type Size
explain.txt text/plain 5.8 KB

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Ranier Vilela 2022-08-25 13:48:06 Re: Select on partitioned table is very slow
Previous Message Laurenz Albe 2022-08-25 10:00:04 Re: Select on partitioned table is very slow