Thank you for the pointer, here is plan now, it is much better:
testdb=# prepare fooplan(character varying,character varying ,bigint,character
varying) 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;
PREPARE
testdb=# explain (analyze, buffers) execute
fooplan('IMXK','B002LA1D9Y','1','SELLABLE');
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=8.46..8.47 rows=1 width=160) (actual time=0.108..0.110 rows=5
loops=1)
Sort Key: cost_acquisition_date
Sort Method: quicksort Memory: 26kB
Buffers: shared hit=8
-> 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.067..0.080 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))
Buffers: shared hit=8
Execution time: 0.238 ms
(8 rows)
Regards,
Virendra Kumar
On Monday, April 12, 2021, 4:18:01 AM PDT, Vijaykumar Jain
<vijaykumar.ta...@gmail.com> wrote:
I am sorry if i have messed up the email original content.
feel free to ignore my diversion.
i forwarded this mail from some other account to work on the issue.
On Mon, Apr 12, 2021 at 4:45 PM Vijaykumar Jain <vijaykumar.ta...@gmail.com>
wrote:
>
> Hi,
>
> I am not sure I am 100% on this, because I have a small machine at home.
>
> but reading PostgreSQL: Documentation: 9.6: PREPARE and
> PostgreSQL - general - bpchar, text and indexes (postgresql-archive.org)
>
> PostgreSQL: Documentation: 9.6: Operators
>
> I think it might be something to do with the parameters types in the prepared
> statement and their casting.
>
> But , the below would be reproducible
>
> postgres=# create table t(t_id int4, sn_c char(20));
> CREATE TABLE
> postgres=# insert into t select id,
> chr((random()*26)::int4+65)||chr((random()*26)::int4+65)||((random()*99999)::int4+1)
> from generate_series(1, 10000) id;
> INSERT 0 10000
> postgres=# create index i_t_sn_c on t(sn_c);
> CREATE INDEX
> postgres=# vacuum analyze t;
> VACUUM
> postgres=# EXPLAIN (analyze, costs off) SELECT sn_c FROM t WHERE sn_c =
> 'AB1234';
> QUERY PLAN
> -------------------------------------------------------------------------------
> Index Only Scan using i_t_sn_c on t (actual time=0.048..0.048 rows=0 loops=1)
> Index Cond: (sn_c = 'AB1234'::bpchar)
> Heap Fetches: 0
> Planning Time: 0.119 ms
> Execution Time: 0.059 ms
> (5 rows)
>
> postgres=# EXPLAIN (analyze, costs off) SELECT sn_c FROM t WHERE sn_c =
> 'AB1234'::text;
> QUERY PLAN
> ---------------------------------------------------------
> Seq Scan on t (actual time=3.037..3.037 rows=0 loops=1)
> Filter: ((sn_c)::text = 'AB1234'::text)
> Rows Removed by Filter: 10000
> Planning Time: 0.057 ms
> Execution Time: 3.047 ms
> (5 rows)
>
> postgres=# prepare qq(text) as SELECT sn_c FROM t WHERE sn_c = $1; // verify
> from select * from pg_prepared_statements.
> PREPARE
> postgres=# explain analyze execute qq('AB1234');
> QUERY PLAN
> -----------------------------------------------------------------------------------------------
> Seq Scan on t (cost=0.00..224.00 rows=50 width=21) (actual
> time=3.156..3.157 rows=0 loops=1)
> Filter: ((sn_c)::text = 'AB1234'::text)
> Rows Removed by Filter: 10000
> Planning Time: 0.069 ms
> Execution Time: 3.168 ms
> (5 rows)
>
> postgres=# prepare qq(text) ^C SELECT sn_c FROM t WHERE sn_c = $1; // verify
> from select * from pg_prepared_statements.
> postgres=# deallocate qq;
> DEALLOCATE
> postgres=# prepare qq(bpchar) as SELECT sn_c FROM t WHERE sn_c = $1;
> PREPARE
> postgres=# explain analyze execute qq('AB1234');
> QUERY PLAN
> ------------------------------------------------------------------------------------------------------------------
> Index Only Scan using i_t_sn_c on t (cost=0.29..4.30 rows=1 width=21)
> (actual time=0.015..0.015 rows=0 loops=1)
> Index Cond: (sn_c = 'AB1234'::bpchar)
> Heap Fetches: 0
> Planning Time: 0.090 ms
> Execution Time: 0.027 ms
> (5 rows)
>
> postgres=# \d t
> Table "public.t"
> Column | Type | Collation | Nullable | Default
> --------+---------------+-----------+----------+---------
> t_id | integer | | |
> sn_c | character(20) | | |
> Indexes:
> "i_t_sn_c" btree (sn_c)
>
> postgres=# deallocate qq;
> DEALLOCATE
> postgres=# prepare qq(bpchar) as SELECT sn_c FROM t WHERE sn_c = $1;
> PREPARE
> postgres=# explain analyze execute qq('AB1234');
> QUERY PLAN
> ------------------------------------------------------------------------------------------------------------------
> Index Only Scan using i_t_sn_c on t (cost=0.29..4.30 rows=1 width=21)
> (actual time=0.015..0.015 rows=0 loops=1)
> Index Cond: (sn_c = 'AB1234'::bpchar)
> Heap Fetches: 0
> Planning Time: 0.074 ms
> Execution Time: 0.027 ms
> (5 rows)
>
> postgres=# deallocate qq;
> DEALLOCATE
> postgres=# prepare qq(text) as SELECT sn_c FROM t WHERE sn_c = $1;
> PREPARE
> postgres=# explain analyze execute qq('AB1234');
> QUERY PLAN
> -----------------------------------------------------------------------------------------------
> Seq Scan on t (cost=0.00..224.00 rows=50 width=21) (actual
> time=3.129..3.130 rows=0 loops=1)
> Filter: ((sn_c)::text = 'AB1234'::text)
> Rows Removed by Filter: 10000
> Planning Time: 0.071 ms
> Execution Time: 3.141 ms
> (5 rows)
>
>
> ---------- Forwarded message ---------
> From: Virendra Kumar <viru_7...@yahoo.com>
> Date: Mon, 12 Apr 2021 at 4:44 AM
> Subject: Prepare Statement VS Literal Values
> To: Pgsql-general General <pgsql-gene...@postgresql.org>
>
>
> 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
>
>
>
>
>
