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 good plan but as soon as I use bind variable using the prepare statement it is going for toss and the same query is taking a 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) (actual time=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 second one 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
