Re: Get more from indices.

Поиск
Список
Период
Сортировка
От Kyotaro HORIGUCHI
Тема Re: Get more from indices.
Дата
Msg-id 20140310.162139.201355356.horiguchi.kyotaro@lab.ntt.co.jp
обсуждение исходный текст
Ответ на Re: Get more from indices.  (Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>)
Ответы Re: Get more from indices.  (Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>)
Re: Get more from indices.  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-hackers
Oops! I found a bug in this patch. The previous v8 patch missed
the case that build_index_pathkeys() could build a partial
pathkeys from the index tlist.

This causes the situation follows,

=======
=# \d cu11    Table "public.cu11"Column |  Type   | Modifiers 
--------+---------+-----------a      | integer | not nullb      | integer | not nullc      | integer | d      | text
|
 
Indexes:   "cu11_a_b_idx" UNIQUE, btree (a, b)

s=# explain (costs off) select * from cu11 order by a, c ,d;             QUERY PLAN               
---------------------------------------Index Scan using cu11_a_b_idx on cu11
(1 row)
=======

Where the simple ORDER BY a, c, d on the table with index on
columns (a, b) results simple index scan which cannot perform the
order.

The attached v9 patche is fixed by adding a check for the case
into index_pathkeys_are_extensible(), and rebase to current HEAD.

regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index bfb4b9f..ff5c88c 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -1790,6 +1790,7 @@ _outIndexOptInfo(StringInfo str, const IndexOptInfo *node)    WRITE_BOOL_FIELD(predOK);
WRITE_BOOL_FIELD(unique);   WRITE_BOOL_FIELD(immediate);
 
+    WRITE_BOOL_FIELD(allnotnull);    WRITE_BOOL_FIELD(hypothetical);    /* we don't bother with fields copied from the
pg_amentry */}
 
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index a912174..4376e95 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -951,8 +951,11 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,    {        index_pathkeys =
build_index_pathkeys(root,index,                                              ForwardScanDirection);
 
-        useful_pathkeys = truncate_useless_pathkeys(root, rel,
-                                                    index_pathkeys);
+        if (index_pathkeys_are_extensible(root, index, index_pathkeys))
+            useful_pathkeys = root->query_pathkeys;
+        else
+            useful_pathkeys = truncate_useless_pathkeys(root, rel,
+                                                        index_pathkeys);        orderbyclauses = NIL;
orderbyclausecols= NIL;    }
 
diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c
index 9179c61..01479f4 100644
--- a/src/backend/optimizer/path/pathkeys.c
+++ b/src/backend/optimizer/path/pathkeys.c
@@ -502,6 +502,65 @@ build_index_pathkeys(PlannerInfo *root,}/*
+ * index_pathkeys_are_extensible
+ *      Check whether the pathkeys are extensible to query_pathkeys.
+ */
+bool
+index_pathkeys_are_extensible(PlannerInfo *root,
+                              IndexOptInfo *index,
+                              List *pathkeys)
+{
+    bool        result;
+    ListCell   *lc1;
+
+    if (root->query_pathkeys == NIL || pathkeys == NIL)
+        return false;
+
+    /* This index is not suitable for pathkey extension */
+    if (!index->unique || !index->immediate || !index->allnotnull)
+        return false;
+
+    /* pathkeys is a prefixing proper subset of index tlist */
+    if (list_length(pathkeys) < list_length(index->indextlist))
+        return false;
+
+    if (!pathkeys_contained_in(pathkeys, root->query_pathkeys))
+        return false;
+
+    if (list_length(pathkeys) == list_length(root->query_pathkeys))
+        return true;
+
+    result = true;
+    foreach(lc1, root->query_pathkeys)
+    {
+        PathKey    *pathkey = (PathKey *) lfirst(lc1);
+        bool        found = false;
+        ListCell   *lc2;
+
+        foreach(lc2, pathkey->pk_eclass->ec_members)
+        {
+            EquivalenceMember *member = (EquivalenceMember *) lfirst(lc2);
+
+            if (!bms_equal(member->em_relids, index->rel->relids) ||
+                !IsA(member->em_expr, Var))
+                continue;
+            else
+            {
+                found = true;
+                break;
+            }
+        }
+
+        if (!found)
+        {
+            result = false;
+            break;
+        }
+    }
+    return result;
+}
+
+/* * build_expression_pathkey *      Build a pathkeys list that describes an ordering by a single expression *
usingthe given sort operator.
 
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 73ba2f6..c61cddb 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -333,6 +333,26 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
info->immediate= index->indimmediate;            info->hypothetical = false;
 
+            info->allnotnull = true;
+            for (i = 0; i < ncolumns; i++)
+            {
+                int            attrno = info->indexkeys[i];
+
+                if (attrno == 0)
+                {
+                    info->allnotnull = false;
+                    break;
+                }
+                else if (attrno > 0)
+                {
+                    if (!relation->rd_att->attrs[attrno - 1]->attnotnull)
+                    {
+                        info->allnotnull = false;
+                        break;
+                    }
+                }
+            }
+            /*             * Estimate the index size.  If it's not a partial index, we lock             * the
number-of-tuplesestimate to equal the parent table; if it
 
diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h
index c607b36..119bb31 100644
--- a/src/include/nodes/relation.h
+++ b/src/include/nodes/relation.h
@@ -525,6 +525,7 @@ typedef struct IndexOptInfo    bool        predOK;            /* true if predicate matches query */
  bool        unique;            /* true if a unique index */    bool        immediate;        /* is uniqueness
enforcedimmediately? */
 
+    bool        allnotnull;        /* true if index's keys are all not null */    bool        hypothetical;    /* true
ifindex doesn't really exist */    bool        canreturn;        /* can index return IndexTuples? */    bool
amcanorderbyop;/* does AM support order by operator result? */
 
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index 9b22fda..8abdb99 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -187,5 +187,8 @@ extern List *truncate_useless_pathkeys(PlannerInfo *root,                          RelOptInfo *rel,
                        List *pathkeys);extern bool has_useful_pathkeys(PlannerInfo *root, RelOptInfo *rel);
 
+extern bool index_pathkeys_are_extensible(PlannerInfo *root,
+                                          IndexOptInfo *index,
+                                          List *pathkeys);#endif   /* PATHS_H */
diff --git a/src/test/isolation/expected/drop-index-concurrently-1.out
b/src/test/isolation/expected/drop-index-concurrently-1.out
index 75dff56..ab96fa0 100644
--- a/src/test/isolation/expected/drop-index-concurrently-1.out
+++ b/src/test/isolation/expected/drop-index-concurrently-1.out
@@ -19,10 +19,8 @@ Sortstep explains: EXPLAIN (COSTS OFF) EXECUTE getrow_seq;QUERY PLAN     
-Sort           
-  Sort Key: id, data
-  ->  Seq Scan on test_dc
-        Filter: ((data)::text = '34'::text)
+Index Scan using test_dc_pkey on test_dc
+  Filter: ((data)::text = '34'::text)step select2: SELECT * FROM test_dc WHERE data=34 ORDER BY id,data;id
data           

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Michael Paquier
Дата:
Сообщение: Re: calculating an aspect of shared buffer state from a background worker
Следующее
От: Haribabu Kommi
Дата:
Сообщение: Re: issue log message to suggest VACUUM FULL if a table is nearly empty