Prepare Statement VS Literal Values - Mailing list pgsql-general

Hi Team,

I am struggling a lot on this for at least 3-4 queries. When I run explain analyze using literal values the query takes
goodplan but as soon as I use bind variable using the prepare statement it is going for toss and the same query is
takinga lot of time. 


testdb=# select version();
                                                       version                                                       
---------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.11 on x86_64-apple-darwin19.6.0, compiled by Apple clang version 12.0.0 (clang-1200.0.32.29), 64-bit
(1 row)

testdb=# \d+ warehouse_costs
                                         Table "public.warehouse_costs"
          Column           |              Type              | Modifiers | Storage  | Stats target | Description
---------------------------+--------------------------------+-----------+----------+--------------+-------------
 warehouse_cost_id         | numeric(38,0)                  | not null  | main     |              |
 asin                      | character(10)                  |           | extended |              |
 warehouse_id              | character(4)                   |           | extended |              |
 item_disposition_code     | character varying(15)          |           | extended |              |
 gl_product_group          | numeric(4,0)                   |           | main     |              |
 base_currency_code        | character varying(15)          |           | extended |              |
 cost_acquisition_date     | timestamp(0) without time zone |           | plain    |              |
 cost                      | numeric(12,2)                  |           | main     |              |
 cost_reference_id         | numeric(38,0)                  |           | main     |              |
 cost_reference_id_source  | character varying(100)         |           | extended |              |
 warehouse_txn_type_id     | numeric(4,0)                   |           | main     |              |
 original_quantity         | integer                        |           | plain    |              |
 on_hand_quantity          | integer                        |           | plain    |              |
 creation_date             | timestamp(0) without time zone |           | plain    |              |
 created_by                | character varying(8)           |           | extended |              |
 last_updated_date         | timestamp(0) without time zone |           | plain    |              |
 last_updated_by           | character varying(8)           |           | extended |              |
 last_updated              | timestamp(0) without time zone |           | plain    |              |
 record_version_number     | numeric(38,0)                  |           | main     |              |
 warehouse_owner_group_id  | numeric(38,0)                  |           | main     |              |
 warehouse_fiscal_owner_id | numeric(38,0)                  |           | main     |              |
Indexes:
    "pk_inventory_costs" PRIMARY KEY, btree (warehouse_cost_id)
    "i_ic_asin_whse_disp_iog_qty" btree (asin, warehouse_id, item_disposition_code, warehouse_owner_group_id,
on_hand_quantity)
    "i_ic_cost_date_asin_iog_id" btree (cost_acquisition_date, asin, warehouse_owner_group_id)
    "i_ic_inventory_txn_type_id" btree (warehouse_txn_type_id)
    "i_ic_last_updated" btree (last_updated)

testdb=# prepare fooplan(text,text,bigint,text) AS select
testdb-# this_.warehouse_cost_id , this_.asin , this_.base_currency_code,
testdb-# this_.cost , this_.cost_acquisition_date , this_.cost_reference_id ,
testdb-# this_.cost_reference_id_source , this_.created_by , this_.creation_date ,
testdb-# this_.gl_product_group , this_.warehouse_fiscal_owner_id ,
testdb-# this_.warehouse_owner_group_id , this_.warehouse_txn_type_id ,
testdb-# this_.item_disposition_code , this_.last_updated , this_.last_updated_by ,
testdb-# this_.last_updated_date , this_.on_hand_quantity , this_.original_quantity ,
testdb-# this_.record_version_number , this_.warehouse_id  from warehouse_costs this_
testdb-# where this_.warehouse_id=$1
testdb-# and this_.asin=$2
testdb-# and this_.warehouse_owner_group_id=$3
testdb-# and this_.item_disposition_code=$4
testdb-# order by this_.cost_acquisition_date asc;
ERROR:  prepared statement "fooplan" already exists
Time: 0.645 ms
testdb=#
testdb=# explain (analyze, buffers) execute fooplan('IMXK','B002LA1D9Y','1','SELLABLE');

explain analyze
select
this_.warehouse_cost_id , this_.asin , this_.base_currency_code ,
this_.cost , this_.cost_acquisition_date , this_.cost_reference_id ,
this_.cost_reference_id_source , this_.created_by , this_.creation_date ,
this_.gl_product_group , this_.warehouse_fiscal_owner_id ,
this_.warehouse_owner_group_id , this_.warehouse_txn_type_id ,
this_.item_disposition_code , this_.last_updated , this_.last_updated_by ,
this_.last_updated_date , this_.on_hand_quantity , this_.original_quantity ,
this_.record_version_number , this_.warehouse_id  from warehouse_costs this_
where this_.warehouse_id='IMXK' and this_.asin='B002LA1D9Y' and this_.warehouse_owner_group_id=1 and
this_.item_disposition_code='SELLABLE'
order by this_.cost_acquisition_date
asc;                                                                                             QUERY
PLAN                                                                                              

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=50630.82..50630.84 rows=7 width=160) (actual time=806.613..806.614 rows=5 loops=1)
   Sort Key: cost_acquisition_date
   Sort Method: quicksort  Memory: 26kB
   Buffers: shared hit=13110 read=13163
   ->  Seq Scan on warehouse_costs this_  (cost=0.00..50630.73 rows=7 width=160) (actual time=341.937..806.582 rows=5
loops=1)
         Filter: ((warehouse_owner_group_id = '1'::numeric) AND ((item_disposition_code)::text = 'SELLABLE'::text) AND
((warehouse_id)::text= 'IMXK'::text) AND ((asin)::text = 'B002LA1D9Y'::text)) 
         Rows Removed by Filter: 974304
         Buffers: shared hit=13110 read=13163
 Execution time: 806.652 ms
(9 rows)

Time: 807.352 ms
testdb=#
testdb=# explain analyze
testdb-# select
testdb-# this_.warehouse_cost_id , this_.asin , this_.base_currency_code ,
testdb-# this_.cost , this_.cost_acquisition_date , this_.cost_reference_id ,
testdb-# this_.cost_reference_id_source , this_.created_by , this_.creation_date ,
testdb-# this_.gl_product_group , this_.warehouse_fiscal_owner_id ,
testdb-# this_.warehouse_owner_group_id , this_.warehouse_txn_type_id ,
testdb-# this_.item_disposition_code , this_.last_updated , this_.last_updated_by ,
testdb-# this_.last_updated_date , this_.on_hand_quantity , this_.original_quantity ,
testdb-# this_.record_version_number , this_.warehouse_id  from warehouse_costs this_
testdb-# where this_.warehouse_id='IMXK' and this_.asin='B002LA1D9Y' and this_.warehouse_owner_group_id=1 and
this_.item_disposition_code='SELLABLE'
testdb-# order by this_.cost_acquisition_date asc;
                                                                                          QUERY
PLAN                                                                                          

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=8.46..8.47 rows=1 width=160) (actual time=0.115..0.116 rows=5 loops=1)
   Sort Key: cost_acquisition_date
   Sort Method: quicksort  Memory: 26kB
   ->  Index Scan using i_ic_asin_whse_disp_iog_qty on warehouse_costs this_  (cost=0.42..8.45 rows=1 width=160)
(actualtime=0.070..0.083 rows=5 loops=1) 
         Index Cond: ((asin = 'B002LA1D9Y'::bpchar) AND (warehouse_id = 'IMXK'::bpchar) AND
((item_disposition_code)::text= 'SELLABLE'::text) AND (warehouse_owner_group_id = '1'::numeric)) 
 Planning time: 0.530 ms
 Execution time: 0.189 ms
(7 rows)

Time: 1.710 ms
testdb=#

--
As we can see the first plan is a prepared statement and is using seq scan of the table and took 800 ms while the
secondone is with literals and index scan and took 0.189 seconds. 

Is there a way I can force the prepared statement to go same as the values with literals.

Thank you in advance!


Regards,
Virendra Kumar