Optimize Postgres query, taking time to execute-CodePudding

I have some sql query that taking time to execute. Is there anyway I can optimize, which will takes few ms to execute.

select
    item0_.dim_Item_ID as dim_item_id ,
    item0_.description as description ,
    vendorfact1_.vendor_id as vendor_id,
    item0_.origin_country as origin_country ,
    item0_.model as model ,
    vendorfact1_.fid as fid,
    item0_.hovbu as hovbu ,
    item0_.is_adhoc_item as is_adhoc_item,
    item0_.destination_country as destination_country,
    item0_.brand as brand,
    item0_.item_Number as item_Number,
    item0_.retailer as retailer,
    item0_.effective_date as effective_date,
    item0_.creation_date as creation_date,
    vendorfact1_.dim_item_relationship_id as dim_item_relationship_id,
    item0_.item_weight as item_weight,
    item0_.in_box_height as in_box_height,
    item0_.in_box_width as in_box_width,
    item0_.in_box_depth as in_box_depth,
    item0_.status as status,
    item0_.status_desc as col_20_0_,
    item0_.primary_image as primary_image,
    item0_.component_materials as component_materials,
    item0_.product_detail as product_detail,
    item0_.finishing_color as finishing_color,
    item0_.packaging_method as packaging_method,
    item0_.packaging_quantity as packaging_quantity,
    item0_.out_box_depth as out_box_depth,
    item0_.out_box_height as out_box_height,
    item0_.out_box_width as out_box_width,
    item0_.net_weight as net_weight,
    item0_.plastic_bag_ind as plastic_bag_ind,
    item0_.itm_pak_qty as itm_pak_qty,
    item0_.barcode as barcode,
    item0_.item_type as item_type,
    item0_.item_cube as item_cube
from
    Item item0_
left outer join vendorfactoryitem vendorfact1_ on
    item0_.hovbu = vendorfact1_.hovbu
    and 
item0_.item_Number = vendorfact1_.item_number
    and item0_.retailer = vendorfact1_.retailer
where
    (lower(cast(item0_.item_Number as varchar(255)))) like ''94749%'
    and 1 = 1
order by
    item0_.item_Number asc
limit 25 offset 0;

Below is the explain, analyze and verbose result of this query :

Limit  (cost=1000.75..65659.66 rows=25 width=360) (actual time=17178.771..17251.398 rows=1 loops=1)
  Output: item0_.dim_item_id, item0_.description, vendorfact1_.vendor_id, item0_.origin_country, item0_.model, vendorfact1_.fid, item0_.hovbu, item0_.is_adhoc_item, item0_.destination_country, item0_.brand, item0_.item_number, item0_.retailer, item0_.effective_date, item0_.creation_date, vendorfact1_.dim_item_relationship_id, item0_.item_weight, item0_.in_box_height, item0_.in_box_width, item0_.in_box_depth, item0_.status, item0_.status_desc, item0_.primary_image, item0_.component_materials, item0_.product_detail, item0_.finishing_color, item0_.packaging_method, item0_.packaging_quantity, item0_.out_box_depth, item0_.out_box_height, item0_.out_box_width, item0_.net_weight, item0_.plastic_bag_ind, item0_.itm_pak_qty, item0_.barcode, item0_.item_type, item0_.item_cube
  ->  Nested Loop Left Join  (cost=1000.75..1123479.50 rows=434 width=360) (actual time=17178.767..17251.380 rows=1 loops=1)
        Output: item0_.dim_item_id, item0_.description, vendorfact1_.vendor_id, item0_.origin_country, item0_.model, vendorfact1_.fid, item0_.hovbu, item0_.is_adhoc_item, item0_.destination_country, item0_.brand, item0_.item_number, item0_.retailer, item0_.effective_date, item0_.creation_date, vendorfact1_.dim_item_relationship_id, item0_.item_weight, item0_.in_box_height, item0_.in_box_width, item0_.in_box_depth, item0_.status, item0_.status_desc, item0_.primary_image, item0_.component_materials, item0_.product_detail, item0_.finishing_color, item0_.packaging_method, item0_.packaging_quantity, item0_.out_box_depth, item0_.out_box_height, item0_.out_box_width, item0_.net_weight, item0_.plastic_bag_ind, item0_.itm_pak_qty, item0_.barcode, item0_.item_type, item0_.item_cube
        ->  Gather Merge  (cost=1000.46..1119804.73 rows=434 width=312) (actual time=17176.186..17248.794 rows=1 loops=1)
              Output: item0_.dim_item_id, item0_.description, item0_.origin_country, item0_.model, item0_.hovbu, item0_.is_adhoc_item, item0_.destination_country, item0_.brand, item0_.item_number, item0_.retailer, item0_.effective_date, item0_.creation_date, item0_.item_weight, item0_.in_box_height, item0_.in_box_width, item0_.in_box_depth, item0_.status, item0_.status_desc, item0_.primary_image, item0_.component_materials, item0_.product_detail, item0_.finishing_color, item0_.packaging_method, item0_.packaging_quantity, item0_.out_box_depth, item0_.out_box_height, item0_.out_box_width, item0_.net_weight, item0_.plastic_bag_ind, item0_.itm_pak_qty, item0_.barcode, item0_.item_type, item0_.item_cube
              Workers Planned: 2
              Workers Launched: 2
              ->  Parallel Index Scan using item_item_number_idx on public.item item0_  (cost=0.43..1118754.61 rows=181 width=312) (actual time=16204.138..17151.023 rows=0 loops=3)
                    Output: item0_.dim_item_id, item0_.description, item0_.origin_country, item0_.model, item0_.hovbu, item0_.is_adhoc_item, item0_.destination_country, item0_.brand, item0_.item_number, item0_.retailer, item0_.effective_date, item0_.creation_date, item0_.item_weight, item0_.in_box_height, item0_.in_box_width, item0_.in_box_depth, item0_.status, item0_.status_desc, item0_.primary_image, item0_.component_materials, item0_.product_detail, item0_.finishing_color, item0_.packaging_method, item0_.packaging_quantity, item0_.out_box_depth, item0_.out_box_height, item0_.out_box_width, item0_.net_weight, item0_.plastic_bag_ind, item0_.itm_pak_qty, item0_.barcode, item0_.item_type, item0_.item_cube
                    Filter: (lower(((item0_.item_number)::character varying(255))::text) ~~ ''94749%'::text)
                    Rows Removed by Filter: 1446882
                    Worker 0:  actual time=17138.559..17138.560 rows=0 loops=1
                    Worker 1:  actual time=14298.765..17139.416 rows=1 loops=1
        ->  Index Scan using vendorfactoryitem_item_number_idx on public.vendorfactoryitem vendorfact1_  (cost=0.29..8.46 rows=1 width=63) (actual time=2.537..2.538 rows=0 loops=1)
              Output: vendorfact1_.dim_item_relationship_id, vendorfact1_.dim_factory_import_id, vendorfact1_.fid, vendorfact1_.factory_short_name, vendorfact1_.company_id, vendorfact1_.vendor_id, vendorfact1_.vendor_short_name, vendorfact1_.hovbu, vendorfact1_.item_id, vendorfact1_.item_number, vendorfact1_.retailer, vendorfact1_.remark, vendorfact1_.is_valid, vendorfact1_.created_by, vendorfact1_.created_on, vendorfact1_.effective_date, vendorfact1_.last_modified_date, vendorfact1_.is_adhoc_rel
              Index Cond: (vendorfact1_.item_number = item0_.item_number)
              Filter: (((item0_.hovbu)::text = (vendorfact1_.hovbu)::text) AND ((item0_.retailer)::text = (vendorfact1_.retailer)::text))
Query Identifier: 6897369345298527260
Planning Time: 12.924 ms
Execution Time: 17252.175 ms

I need the result set within 6 to 7 ms. Postgres version: PostgreSQL 14.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit

CodePudding user response：

You need to index your weird pattern matching condition:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX ON item USING gin (lower(cast(item0_.item_Number as varchar(255))) gin_trgm_ops);

You need a trigram index because you have a pattern that is not anchored.