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
