CODEWITHSUNDEEP
sqlpostgresqlquery-performance
Ratatouille
Ratatouille

Reputation: 1492

why does PREPARE statement not respect INDEXing

Help me understand as to why query evaluated using prepared statement does not respect indexing.

\d+ lerg;

                                                        Table "public.lerg"
   Column   |            Type             |                     Modifiers                     | Storage  | Stats target | Description
------------+-----------------------------+---------------------------------------------------+----------+--------------+-------------
 id         | bigint                      | not null default nextval('lerg_id_seq'::regclass) | plain    |              |
 lata       | character varying           | not null                                          | extended |              |
 npa        | character varying(3)        |                                                   | extended |              |
 nxx        | character varying(3)        |                                                   | extended |              |
 block      | character varying(1)        |                                                   | extended |              |
 ocn        | character varying           |                                                   | extended |              |
 created_at | timestamp without time zone | not null default now()                            | plain    |              |
Indexes:
    "lerg_pkey" PRIMARY KEY, btree (id)
    "lerg_npa_nxx_block" UNIQUE, btree (npa, nxx, block)

Total number of record in the LERG table.

select count(*) from lerg; 
=> 199846

The prepared statement 

   prepare fetch_lata(char(3), char(3), char(1)) as select lata from lerg where npa=$1 and nxx=$2 and block=$3;

execute statement.

    explain analyze execute fetch_lata('365','406','A');
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Seq Scan on lerg  (cost=0.00..5163.31 rows=1 width=6) (actual time=0.014..27.530 rows=1 loops=1)
   Filter: (((npa)::bpchar = '365'::bpchar) AND ((nxx)::bpchar = '406'::bpchar) AND ((block)::bpchar = 'A'::bpchar))
   Rows Removed by Filter: 199845
 Execution time: 27.560 ms
(4 rows)

Not able to understand.

On other note.

explain analyze select lata from lerg where npa='365' and nxx='406' and block='A';
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Index Scan using lerg_npa_nxx_block on lerg  (cost=0.42..8.44 rows=1 width=6) (actual time=0.016..0.016 rows=1 loops=1)
   Index Cond: (((npa)::text = '365'::text) AND ((nxx)::text = '406'::text) AND ((block)::text = 'A'::text))
 Planning time: 0.081 ms
 Execution time: 0.033 ms
(4 rows)

Q. A select query with proper indexing beats hands down the prepared statement query, why ?

Q. Are there no advantage of using PREPARED statement for simple query like the above.

Just a head's up the difference for query 10_000 record was so high that I have to give up on prepared statement.

The result of query 10_000 record.

                                   user      system      total      real
pg_without_prepared_statmt        0.050000   0.080000   0.130000 (  0.935051)

pg_with_prepared_statmt           0.090000   0.110000   0.200000 (  5.707693)

Machine details.

Postgres Version : 9.5.9 
Mac OS: 10.12.5

Upvotes: 1

Views: 199

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247270

The problem is that the parameters for your prepared statement are of type character (the same as bpchar), so the equality operator for that type is used, and the index cannot be used because it is defined with character varying.

If you change the arguments of the prepared statement to varchar, it should work as expected.

Upvotes: 2

Related Questions