为什么准备声明不尊重INDEXing？

Question

帮助我理解为什么使用准备语句计算的查询不尊重索引。

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

LERG表中的记录总数。

select count(*) from lerg; 
=> 199846

准备好的声明

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

执行语句。

    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)

无法理解。

• 为什么要扫描桌子？
• 为什么没有使用索引？

换个音符。

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.具有适当索引的select查询胜过准备语句查询，为什么？

Q.没有使用PREPARED语句进行像上面这样的简单查询的好处。

查询10_000记录的差异太大了，我不得不放弃准备好的语句。

查询10_000记录的结果。

                                   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)

机器细节。

Postgres Version : 9.5.9 
Mac OS: 10.12.5

Answer 1

问题是，准备好的语句的参数类型为character (与bpchar相同)，因此使用了该类型的相等运算符，而且索引不能使用，因为它是用character varying定义的。

如果您将准备好的语句的参数更改为varchar，它应该会像预期的那样工作。