PostgreSQL游标与索引选择实例详细介绍

bill=# create table tbl (id int , c1 int , c2 int , c3 int , c4 int );

CREATE TABLE

bill=# insert into tbl select generate_series(1,10000000), random()*100, random()*100, random()*100, random()*100; 

INSERT 0 10000000 

bill=# insert into tbl select generate_series(10000001,11000000), 200,200,200,200; 

INSERT 0 1000000 

bill=# create index idx_tbl_1 on tbl(id); 

CREATE INDEX

bill=# create index idx_tbl_2 on tbl(c1,c2,c3,c4); 

CREATE INDEX

bill=# vacuum analyze tbl; 

VACUUM 

bill=# explain select * from tbl where c1=200 and c2=200 order by id;

                                      QUERY PLAN

-------------------------------------------------------------------------------------

  Sort  (cost=72109.20..72344.16 rows =93984 width=20)

    Sort Key : id

    ->  Bitmap Heap Scan on tbl  (cost=1392.77..60811.81 rows =93984 width=20)

          Recheck Cond: ((c1 = 200) AND (c2 = 200))

          ->  Bitmap Index Scan on idx_tbl_2  (cost=0.00..1369.28 rows =93984 width=0)

                Index Cond: ((c1 = 200) AND (c2 = 200))

(6 rows )

bill=# begin ;

BEGIN

bill=*# explain declare tt cursor for select * from tbl where c1=200 and c2=200 order by id;

                                   QUERY PLAN

-------------------------------------------------------------------------------

  Index Scan using idx_tbl_1 on tbl  (cost=0.43..329277.60 rows =93984 width=20)

    Filter: ((c1 = 200) AND (c2 = 200))

(2 rows )

导致选择了错误的执行计划。

     /* Determine what fraction of the plan is likely to be scanned */

     if (cursorOptions & CURSOR_OPT_FAST_PLAN)

     {

         /*

          * We have no real idea how many tuples the user will ultimately FETCH

          * from a cursor, but it is often the case that he doesn't want 'em

          * all, or would prefer a fast-start plan anyway so that he can

          * process some of the tuples sooner.  Use a GUC parameter to decide

          * what fraction to optimize for.

          */

         tuple_fraction = cursor_tuple_fraction;

         /*

          * We document cursor_tuple_fraction as simply being a fraction, which

          * means the edge cases 0 and 1 have to be treated specially here.  We

          * convert 1 to 0 ("all the tuples") and 0 to a very small fraction.

          */

         if (tuple_fraction >= 1.0)

             tuple_fraction = 0.0;

         else if (tuple_fraction <= 0.0)

             tuple_fraction = 1e-10;

     }

     else

     {

         /* Default assumption is we need all the tuples */

         tuple_fraction = 0.0;

     }