Prepare Statement VS Literal Values

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 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