Re: Prepare Statement VS Literal Values - Mailing list pgsql-general

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)
(actualtime=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.tater@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.tater@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_7683@yahoo.com>
> Date: Mon, 12 Apr 2021 at 4:44 AM
> Subject: Prepare Statement VS Literal Values
> To: Pgsql-general General <pgsql-general@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
takesgood plan but as soon as I use bind variable using the prepare statement it is going for toss and the same query
istaking 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)
(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
>
>
>
>
>