public inbox for [email protected]
help / color / mirror / Atom feed[PATCH 8/8] Consider selectivity in choose_bitmap_and
28+ messages / 4 participants
[nested] [flat]
* [PATCH 8/8] Consider selectivity in choose_bitmap_and
@ 2021-03-11 11:57 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 28+ messages in thread
From: Tomas Vondra @ 2021-03-11 11:57 UTC (permalink / raw)
---
src/backend/optimizer/path/indxpath.c | 38 ++++++++++++++++-----------
1 file changed, 23 insertions(+), 15 deletions(-)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index ff536e6b24..36b29c6c7b 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1437,6 +1437,7 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
foreach(l, paths)
{
Path *ipath = (Path *) lfirst(l);
+ bool duplicate = false;
pathinfo = classify_index_clause_usage(ipath, &clauselist);
@@ -1451,22 +1452,29 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
{
if (!pathinfoarray[i]->unclassifiable &&
bms_equal(pathinfo->clauseids, pathinfoarray[i]->clauseids))
- break;
- }
- if (i < npaths)
- {
- /* duplicate clauseids, keep the cheaper one */
- Cost ncost;
- Cost ocost;
- Selectivity nselec;
- Selectivity oselec;
-
- cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
- cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
- if (ncost < ocost)
- pathinfoarray[i] = pathinfo;
+ {
+ /* duplicate clauseids, keep the cheaper one
+ *
+ * XXX maybe this should just use path_usage_comparator?
+ */
+ Cost ncost;
+ Cost ocost;
+ Selectivity nselec;
+ Selectivity oselec;
+
+ cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
+ cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
+
+ if ((ncost < ocost) && (nselec < oselec))
+ {
+ pathinfoarray[i] = pathinfo;
+ duplicate = true;
+ break;
+ }
+ }
}
- else
+
+ if (!duplicate)
{
/* not duplicate clauseids, add to array */
pathinfoarray[npaths++] = pathinfo;
--
2.26.2
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproduce2.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproduce2.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0KGEgYmlnaW50KSB3aXRoIChmaWxsZmFjdG9y
ID0gMjUpOwoKaW5zZXJ0IGludG8gdCBzZWxlY3QgaSBmcm9tIGdlbmVyYXRlX3Nlcmllcygx
LDEwMDAwMDAwKSBzKGkpOwoKY3JlYXRlIGluZGV4IGlkeF8xIG9uIHQgdXNpbmcgYnJpbiAo
YSkgd2l0aCAocGFnZXNfcGVyX3JhbmdlID0gNCk7CgpjcmVhdGUgaW5kZXggaWR4XzIgb24g
dCB1c2luZyBicmluIChhKTsKCmFuYWx5emUgdDsKClxkKwpcZGkrCgpleHBsYWluIGFuYWx5
emUgc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJvcCBpbmRleCBpZHhfMjsK
CmV4cGxhaW4gYW5hbHl6ZSBzZWxlY3QgKiBmcm9tIHQgd2hlcmUgYSA9IDEwMDA7Cg==
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproducer.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproducer.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0IChhIGludCwgYiBiaWdpbnQsIGMgdGV4dCkg
d2l0aCAoZmlsbGZhY3RvciA9IDUwKTsKCmluc2VydCBpbnRvIHQgc2VsZWN0IDEwMDAwMDAg
KiByYW5kb20oKSwgMTAwMDAwMCAqIHJhbmRvbSgpLCBtZDUoKDEwMDAwMDAgKiByYW5kb20o
KSk6OmludDo6dGV4dCkgZnJvbSBnZW5lcmF0ZV9zZXJpZXMoMSwxMDAwMDAwKSBzKGkpOwoK
c2VsZWN0IGNvdW50KCopIGZyb20gKHNlbGVjdCBkaXN0aW5jdCBiIGZyb20gdCB3aGVyZSBj
dGlkIDwgJygxMjgsMCknOjp0aWQpIGZvbzsKCmNyZWF0ZSBpbmRleCBibG9vbV9hX2lkeCBv
biB0IHVzaW5nIGJyaW4gKGEgaW50NF9ibG9vbV9vcHMobl9kaXN0aW5jdF9wZXJfcmFuZ2U9
NjAwMCkpOyAKCmFuYWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEg
PSAxMDAwOwoKY3JlYXRlIGluZGV4IGJyaW5faWR4IG9uIHQgdXNpbmcgYnJpbiAoYSk7CmFu
YWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJv
cCBpbmRleCBicmluX2lkeDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAx
MDAwOwo=
--------------D524CD37E77B36A2883A7617--
^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH 8/8] Consider selectivity in choose_bitmap_and
@ 2021-03-11 11:57 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 28+ messages in thread
From: Tomas Vondra @ 2021-03-11 11:57 UTC (permalink / raw)
---
src/backend/optimizer/path/indxpath.c | 38 ++++++++++++++++-----------
1 file changed, 23 insertions(+), 15 deletions(-)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index ff536e6b24..36b29c6c7b 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1437,6 +1437,7 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
foreach(l, paths)
{
Path *ipath = (Path *) lfirst(l);
+ bool duplicate = false;
pathinfo = classify_index_clause_usage(ipath, &clauselist);
@@ -1451,22 +1452,29 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
{
if (!pathinfoarray[i]->unclassifiable &&
bms_equal(pathinfo->clauseids, pathinfoarray[i]->clauseids))
- break;
- }
- if (i < npaths)
- {
- /* duplicate clauseids, keep the cheaper one */
- Cost ncost;
- Cost ocost;
- Selectivity nselec;
- Selectivity oselec;
-
- cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
- cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
- if (ncost < ocost)
- pathinfoarray[i] = pathinfo;
+ {
+ /* duplicate clauseids, keep the cheaper one
+ *
+ * XXX maybe this should just use path_usage_comparator?
+ */
+ Cost ncost;
+ Cost ocost;
+ Selectivity nselec;
+ Selectivity oselec;
+
+ cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
+ cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
+
+ if ((ncost < ocost) && (nselec < oselec))
+ {
+ pathinfoarray[i] = pathinfo;
+ duplicate = true;
+ break;
+ }
+ }
}
- else
+
+ if (!duplicate)
{
/* not duplicate clauseids, add to array */
pathinfoarray[npaths++] = pathinfo;
--
2.26.2
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproduce2.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproduce2.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0KGEgYmlnaW50KSB3aXRoIChmaWxsZmFjdG9y
ID0gMjUpOwoKaW5zZXJ0IGludG8gdCBzZWxlY3QgaSBmcm9tIGdlbmVyYXRlX3Nlcmllcygx
LDEwMDAwMDAwKSBzKGkpOwoKY3JlYXRlIGluZGV4IGlkeF8xIG9uIHQgdXNpbmcgYnJpbiAo
YSkgd2l0aCAocGFnZXNfcGVyX3JhbmdlID0gNCk7CgpjcmVhdGUgaW5kZXggaWR4XzIgb24g
dCB1c2luZyBicmluIChhKTsKCmFuYWx5emUgdDsKClxkKwpcZGkrCgpleHBsYWluIGFuYWx5
emUgc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJvcCBpbmRleCBpZHhfMjsK
CmV4cGxhaW4gYW5hbHl6ZSBzZWxlY3QgKiBmcm9tIHQgd2hlcmUgYSA9IDEwMDA7Cg==
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproducer.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproducer.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0IChhIGludCwgYiBiaWdpbnQsIGMgdGV4dCkg
d2l0aCAoZmlsbGZhY3RvciA9IDUwKTsKCmluc2VydCBpbnRvIHQgc2VsZWN0IDEwMDAwMDAg
KiByYW5kb20oKSwgMTAwMDAwMCAqIHJhbmRvbSgpLCBtZDUoKDEwMDAwMDAgKiByYW5kb20o
KSk6OmludDo6dGV4dCkgZnJvbSBnZW5lcmF0ZV9zZXJpZXMoMSwxMDAwMDAwKSBzKGkpOwoK
c2VsZWN0IGNvdW50KCopIGZyb20gKHNlbGVjdCBkaXN0aW5jdCBiIGZyb20gdCB3aGVyZSBj
dGlkIDwgJygxMjgsMCknOjp0aWQpIGZvbzsKCmNyZWF0ZSBpbmRleCBibG9vbV9hX2lkeCBv
biB0IHVzaW5nIGJyaW4gKGEgaW50NF9ibG9vbV9vcHMobl9kaXN0aW5jdF9wZXJfcmFuZ2U9
NjAwMCkpOyAKCmFuYWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEg
PSAxMDAwOwoKY3JlYXRlIGluZGV4IGJyaW5faWR4IG9uIHQgdXNpbmcgYnJpbiAoYSk7CmFu
YWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJv
cCBpbmRleCBicmluX2lkeDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAx
MDAwOwo=
--------------D524CD37E77B36A2883A7617--
^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH 8/8] Consider selectivity in choose_bitmap_and
@ 2021-03-11 11:57 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 28+ messages in thread
From: Tomas Vondra @ 2021-03-11 11:57 UTC (permalink / raw)
---
src/backend/optimizer/path/indxpath.c | 38 ++++++++++++++++-----------
1 file changed, 23 insertions(+), 15 deletions(-)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index ff536e6b24..36b29c6c7b 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1437,6 +1437,7 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
foreach(l, paths)
{
Path *ipath = (Path *) lfirst(l);
+ bool duplicate = false;
pathinfo = classify_index_clause_usage(ipath, &clauselist);
@@ -1451,22 +1452,29 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
{
if (!pathinfoarray[i]->unclassifiable &&
bms_equal(pathinfo->clauseids, pathinfoarray[i]->clauseids))
- break;
- }
- if (i < npaths)
- {
- /* duplicate clauseids, keep the cheaper one */
- Cost ncost;
- Cost ocost;
- Selectivity nselec;
- Selectivity oselec;
-
- cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
- cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
- if (ncost < ocost)
- pathinfoarray[i] = pathinfo;
+ {
+ /* duplicate clauseids, keep the cheaper one
+ *
+ * XXX maybe this should just use path_usage_comparator?
+ */
+ Cost ncost;
+ Cost ocost;
+ Selectivity nselec;
+ Selectivity oselec;
+
+ cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
+ cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
+
+ if ((ncost < ocost) && (nselec < oselec))
+ {
+ pathinfoarray[i] = pathinfo;
+ duplicate = true;
+ break;
+ }
+ }
}
- else
+
+ if (!duplicate)
{
/* not duplicate clauseids, add to array */
pathinfoarray[npaths++] = pathinfo;
--
2.26.2
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproduce2.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproduce2.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0KGEgYmlnaW50KSB3aXRoIChmaWxsZmFjdG9y
ID0gMjUpOwoKaW5zZXJ0IGludG8gdCBzZWxlY3QgaSBmcm9tIGdlbmVyYXRlX3Nlcmllcygx
LDEwMDAwMDAwKSBzKGkpOwoKY3JlYXRlIGluZGV4IGlkeF8xIG9uIHQgdXNpbmcgYnJpbiAo
YSkgd2l0aCAocGFnZXNfcGVyX3JhbmdlID0gNCk7CgpjcmVhdGUgaW5kZXggaWR4XzIgb24g
dCB1c2luZyBicmluIChhKTsKCmFuYWx5emUgdDsKClxkKwpcZGkrCgpleHBsYWluIGFuYWx5
emUgc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJvcCBpbmRleCBpZHhfMjsK
CmV4cGxhaW4gYW5hbHl6ZSBzZWxlY3QgKiBmcm9tIHQgd2hlcmUgYSA9IDEwMDA7Cg==
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproducer.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproducer.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0IChhIGludCwgYiBiaWdpbnQsIGMgdGV4dCkg
d2l0aCAoZmlsbGZhY3RvciA9IDUwKTsKCmluc2VydCBpbnRvIHQgc2VsZWN0IDEwMDAwMDAg
KiByYW5kb20oKSwgMTAwMDAwMCAqIHJhbmRvbSgpLCBtZDUoKDEwMDAwMDAgKiByYW5kb20o
KSk6OmludDo6dGV4dCkgZnJvbSBnZW5lcmF0ZV9zZXJpZXMoMSwxMDAwMDAwKSBzKGkpOwoK
c2VsZWN0IGNvdW50KCopIGZyb20gKHNlbGVjdCBkaXN0aW5jdCBiIGZyb20gdCB3aGVyZSBj
dGlkIDwgJygxMjgsMCknOjp0aWQpIGZvbzsKCmNyZWF0ZSBpbmRleCBibG9vbV9hX2lkeCBv
biB0IHVzaW5nIGJyaW4gKGEgaW50NF9ibG9vbV9vcHMobl9kaXN0aW5jdF9wZXJfcmFuZ2U9
NjAwMCkpOyAKCmFuYWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEg
PSAxMDAwOwoKY3JlYXRlIGluZGV4IGJyaW5faWR4IG9uIHQgdXNpbmcgYnJpbiAoYSk7CmFu
YWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJv
cCBpbmRleCBicmluX2lkeDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAx
MDAwOwo=
--------------D524CD37E77B36A2883A7617--
^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH 8/8] Consider selectivity in choose_bitmap_and
@ 2021-03-11 11:57 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 28+ messages in thread
From: Tomas Vondra @ 2021-03-11 11:57 UTC (permalink / raw)
---
src/backend/optimizer/path/indxpath.c | 38 ++++++++++++++++-----------
1 file changed, 23 insertions(+), 15 deletions(-)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index ff536e6b24..36b29c6c7b 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1437,6 +1437,7 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
foreach(l, paths)
{
Path *ipath = (Path *) lfirst(l);
+ bool duplicate = false;
pathinfo = classify_index_clause_usage(ipath, &clauselist);
@@ -1451,22 +1452,29 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
{
if (!pathinfoarray[i]->unclassifiable &&
bms_equal(pathinfo->clauseids, pathinfoarray[i]->clauseids))
- break;
- }
- if (i < npaths)
- {
- /* duplicate clauseids, keep the cheaper one */
- Cost ncost;
- Cost ocost;
- Selectivity nselec;
- Selectivity oselec;
-
- cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
- cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
- if (ncost < ocost)
- pathinfoarray[i] = pathinfo;
+ {
+ /* duplicate clauseids, keep the cheaper one
+ *
+ * XXX maybe this should just use path_usage_comparator?
+ */
+ Cost ncost;
+ Cost ocost;
+ Selectivity nselec;
+ Selectivity oselec;
+
+ cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
+ cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
+
+ if ((ncost < ocost) && (nselec < oselec))
+ {
+ pathinfoarray[i] = pathinfo;
+ duplicate = true;
+ break;
+ }
+ }
}
- else
+
+ if (!duplicate)
{
/* not duplicate clauseids, add to array */
pathinfoarray[npaths++] = pathinfo;
--
2.26.2
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproduce2.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproduce2.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0KGEgYmlnaW50KSB3aXRoIChmaWxsZmFjdG9y
ID0gMjUpOwoKaW5zZXJ0IGludG8gdCBzZWxlY3QgaSBmcm9tIGdlbmVyYXRlX3Nlcmllcygx
LDEwMDAwMDAwKSBzKGkpOwoKY3JlYXRlIGluZGV4IGlkeF8xIG9uIHQgdXNpbmcgYnJpbiAo
YSkgd2l0aCAocGFnZXNfcGVyX3JhbmdlID0gNCk7CgpjcmVhdGUgaW5kZXggaWR4XzIgb24g
dCB1c2luZyBicmluIChhKTsKCmFuYWx5emUgdDsKClxkKwpcZGkrCgpleHBsYWluIGFuYWx5
emUgc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJvcCBpbmRleCBpZHhfMjsK
CmV4cGxhaW4gYW5hbHl6ZSBzZWxlY3QgKiBmcm9tIHQgd2hlcmUgYSA9IDEwMDA7Cg==
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproducer.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproducer.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0IChhIGludCwgYiBiaWdpbnQsIGMgdGV4dCkg
d2l0aCAoZmlsbGZhY3RvciA9IDUwKTsKCmluc2VydCBpbnRvIHQgc2VsZWN0IDEwMDAwMDAg
KiByYW5kb20oKSwgMTAwMDAwMCAqIHJhbmRvbSgpLCBtZDUoKDEwMDAwMDAgKiByYW5kb20o
KSk6OmludDo6dGV4dCkgZnJvbSBnZW5lcmF0ZV9zZXJpZXMoMSwxMDAwMDAwKSBzKGkpOwoK
c2VsZWN0IGNvdW50KCopIGZyb20gKHNlbGVjdCBkaXN0aW5jdCBiIGZyb20gdCB3aGVyZSBj
dGlkIDwgJygxMjgsMCknOjp0aWQpIGZvbzsKCmNyZWF0ZSBpbmRleCBibG9vbV9hX2lkeCBv
biB0IHVzaW5nIGJyaW4gKGEgaW50NF9ibG9vbV9vcHMobl9kaXN0aW5jdF9wZXJfcmFuZ2U9
NjAwMCkpOyAKCmFuYWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEg
PSAxMDAwOwoKY3JlYXRlIGluZGV4IGJyaW5faWR4IG9uIHQgdXNpbmcgYnJpbiAoYSk7CmFu
YWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJv
cCBpbmRleCBicmluX2lkeDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAx
MDAwOwo=
--------------D524CD37E77B36A2883A7617--
^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH 8/8] Consider selectivity in choose_bitmap_and
@ 2021-03-11 11:57 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 28+ messages in thread
From: Tomas Vondra @ 2021-03-11 11:57 UTC (permalink / raw)
---
src/backend/optimizer/path/indxpath.c | 38 ++++++++++++++++-----------
1 file changed, 23 insertions(+), 15 deletions(-)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index ff536e6b24..36b29c6c7b 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1437,6 +1437,7 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
foreach(l, paths)
{
Path *ipath = (Path *) lfirst(l);
+ bool duplicate = false;
pathinfo = classify_index_clause_usage(ipath, &clauselist);
@@ -1451,22 +1452,29 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
{
if (!pathinfoarray[i]->unclassifiable &&
bms_equal(pathinfo->clauseids, pathinfoarray[i]->clauseids))
- break;
- }
- if (i < npaths)
- {
- /* duplicate clauseids, keep the cheaper one */
- Cost ncost;
- Cost ocost;
- Selectivity nselec;
- Selectivity oselec;
-
- cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
- cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
- if (ncost < ocost)
- pathinfoarray[i] = pathinfo;
+ {
+ /* duplicate clauseids, keep the cheaper one
+ *
+ * XXX maybe this should just use path_usage_comparator?
+ */
+ Cost ncost;
+ Cost ocost;
+ Selectivity nselec;
+ Selectivity oselec;
+
+ cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
+ cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
+
+ if ((ncost < ocost) && (nselec < oselec))
+ {
+ pathinfoarray[i] = pathinfo;
+ duplicate = true;
+ break;
+ }
+ }
}
- else
+
+ if (!duplicate)
{
/* not duplicate clauseids, add to array */
pathinfoarray[npaths++] = pathinfo;
--
2.26.2
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproduce2.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproduce2.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0KGEgYmlnaW50KSB3aXRoIChmaWxsZmFjdG9y
ID0gMjUpOwoKaW5zZXJ0IGludG8gdCBzZWxlY3QgaSBmcm9tIGdlbmVyYXRlX3Nlcmllcygx
LDEwMDAwMDAwKSBzKGkpOwoKY3JlYXRlIGluZGV4IGlkeF8xIG9uIHQgdXNpbmcgYnJpbiAo
YSkgd2l0aCAocGFnZXNfcGVyX3JhbmdlID0gNCk7CgpjcmVhdGUgaW5kZXggaWR4XzIgb24g
dCB1c2luZyBicmluIChhKTsKCmFuYWx5emUgdDsKClxkKwpcZGkrCgpleHBsYWluIGFuYWx5
emUgc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJvcCBpbmRleCBpZHhfMjsK
CmV4cGxhaW4gYW5hbHl6ZSBzZWxlY3QgKiBmcm9tIHQgd2hlcmUgYSA9IDEwMDA7Cg==
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproducer.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproducer.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0IChhIGludCwgYiBiaWdpbnQsIGMgdGV4dCkg
d2l0aCAoZmlsbGZhY3RvciA9IDUwKTsKCmluc2VydCBpbnRvIHQgc2VsZWN0IDEwMDAwMDAg
KiByYW5kb20oKSwgMTAwMDAwMCAqIHJhbmRvbSgpLCBtZDUoKDEwMDAwMDAgKiByYW5kb20o
KSk6OmludDo6dGV4dCkgZnJvbSBnZW5lcmF0ZV9zZXJpZXMoMSwxMDAwMDAwKSBzKGkpOwoK
c2VsZWN0IGNvdW50KCopIGZyb20gKHNlbGVjdCBkaXN0aW5jdCBiIGZyb20gdCB3aGVyZSBj
dGlkIDwgJygxMjgsMCknOjp0aWQpIGZvbzsKCmNyZWF0ZSBpbmRleCBibG9vbV9hX2lkeCBv
biB0IHVzaW5nIGJyaW4gKGEgaW50NF9ibG9vbV9vcHMobl9kaXN0aW5jdF9wZXJfcmFuZ2U9
NjAwMCkpOyAKCmFuYWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEg
PSAxMDAwOwoKY3JlYXRlIGluZGV4IGJyaW5faWR4IG9uIHQgdXNpbmcgYnJpbiAoYSk7CmFu
YWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJv
cCBpbmRleCBicmluX2lkeDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAx
MDAwOwo=
--------------D524CD37E77B36A2883A7617--
^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH 8/8] Consider selectivity in choose_bitmap_and
@ 2021-03-11 11:57 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 28+ messages in thread
From: Tomas Vondra @ 2021-03-11 11:57 UTC (permalink / raw)
---
src/backend/optimizer/path/indxpath.c | 38 ++++++++++++++++-----------
1 file changed, 23 insertions(+), 15 deletions(-)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index ff536e6b24..36b29c6c7b 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1437,6 +1437,7 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
foreach(l, paths)
{
Path *ipath = (Path *) lfirst(l);
+ bool duplicate = false;
pathinfo = classify_index_clause_usage(ipath, &clauselist);
@@ -1451,22 +1452,29 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
{
if (!pathinfoarray[i]->unclassifiable &&
bms_equal(pathinfo->clauseids, pathinfoarray[i]->clauseids))
- break;
- }
- if (i < npaths)
- {
- /* duplicate clauseids, keep the cheaper one */
- Cost ncost;
- Cost ocost;
- Selectivity nselec;
- Selectivity oselec;
-
- cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
- cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
- if (ncost < ocost)
- pathinfoarray[i] = pathinfo;
+ {
+ /* duplicate clauseids, keep the cheaper one
+ *
+ * XXX maybe this should just use path_usage_comparator?
+ */
+ Cost ncost;
+ Cost ocost;
+ Selectivity nselec;
+ Selectivity oselec;
+
+ cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
+ cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
+
+ if ((ncost < ocost) && (nselec < oselec))
+ {
+ pathinfoarray[i] = pathinfo;
+ duplicate = true;
+ break;
+ }
+ }
}
- else
+
+ if (!duplicate)
{
/* not duplicate clauseids, add to array */
pathinfoarray[npaths++] = pathinfo;
--
2.26.2
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproduce2.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproduce2.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0KGEgYmlnaW50KSB3aXRoIChmaWxsZmFjdG9y
ID0gMjUpOwoKaW5zZXJ0IGludG8gdCBzZWxlY3QgaSBmcm9tIGdlbmVyYXRlX3Nlcmllcygx
LDEwMDAwMDAwKSBzKGkpOwoKY3JlYXRlIGluZGV4IGlkeF8xIG9uIHQgdXNpbmcgYnJpbiAo
YSkgd2l0aCAocGFnZXNfcGVyX3JhbmdlID0gNCk7CgpjcmVhdGUgaW5kZXggaWR4XzIgb24g
dCB1c2luZyBicmluIChhKTsKCmFuYWx5emUgdDsKClxkKwpcZGkrCgpleHBsYWluIGFuYWx5
emUgc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJvcCBpbmRleCBpZHhfMjsK
CmV4cGxhaW4gYW5hbHl6ZSBzZWxlY3QgKiBmcm9tIHQgd2hlcmUgYSA9IDEwMDA7Cg==
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproducer.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproducer.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0IChhIGludCwgYiBiaWdpbnQsIGMgdGV4dCkg
d2l0aCAoZmlsbGZhY3RvciA9IDUwKTsKCmluc2VydCBpbnRvIHQgc2VsZWN0IDEwMDAwMDAg
KiByYW5kb20oKSwgMTAwMDAwMCAqIHJhbmRvbSgpLCBtZDUoKDEwMDAwMDAgKiByYW5kb20o
KSk6OmludDo6dGV4dCkgZnJvbSBnZW5lcmF0ZV9zZXJpZXMoMSwxMDAwMDAwKSBzKGkpOwoK
c2VsZWN0IGNvdW50KCopIGZyb20gKHNlbGVjdCBkaXN0aW5jdCBiIGZyb20gdCB3aGVyZSBj
dGlkIDwgJygxMjgsMCknOjp0aWQpIGZvbzsKCmNyZWF0ZSBpbmRleCBibG9vbV9hX2lkeCBv
biB0IHVzaW5nIGJyaW4gKGEgaW50NF9ibG9vbV9vcHMobl9kaXN0aW5jdF9wZXJfcmFuZ2U9
NjAwMCkpOyAKCmFuYWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEg
PSAxMDAwOwoKY3JlYXRlIGluZGV4IGJyaW5faWR4IG9uIHQgdXNpbmcgYnJpbiAoYSk7CmFu
YWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJv
cCBpbmRleCBicmluX2lkeDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAx
MDAwOwo=
--------------D524CD37E77B36A2883A7617--
^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH 8/8] Consider selectivity in choose_bitmap_and
@ 2021-03-11 11:57 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 28+ messages in thread
From: Tomas Vondra @ 2021-03-11 11:57 UTC (permalink / raw)
---
src/backend/optimizer/path/indxpath.c | 38 ++++++++++++++++-----------
1 file changed, 23 insertions(+), 15 deletions(-)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index ff536e6b24..36b29c6c7b 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1437,6 +1437,7 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
foreach(l, paths)
{
Path *ipath = (Path *) lfirst(l);
+ bool duplicate = false;
pathinfo = classify_index_clause_usage(ipath, &clauselist);
@@ -1451,22 +1452,29 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
{
if (!pathinfoarray[i]->unclassifiable &&
bms_equal(pathinfo->clauseids, pathinfoarray[i]->clauseids))
- break;
- }
- if (i < npaths)
- {
- /* duplicate clauseids, keep the cheaper one */
- Cost ncost;
- Cost ocost;
- Selectivity nselec;
- Selectivity oselec;
-
- cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
- cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
- if (ncost < ocost)
- pathinfoarray[i] = pathinfo;
+ {
+ /* duplicate clauseids, keep the cheaper one
+ *
+ * XXX maybe this should just use path_usage_comparator?
+ */
+ Cost ncost;
+ Cost ocost;
+ Selectivity nselec;
+ Selectivity oselec;
+
+ cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
+ cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
+
+ if ((ncost < ocost) && (nselec < oselec))
+ {
+ pathinfoarray[i] = pathinfo;
+ duplicate = true;
+ break;
+ }
+ }
}
- else
+
+ if (!duplicate)
{
/* not duplicate clauseids, add to array */
pathinfoarray[npaths++] = pathinfo;
--
2.26.2
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproduce2.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproduce2.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0KGEgYmlnaW50KSB3aXRoIChmaWxsZmFjdG9y
ID0gMjUpOwoKaW5zZXJ0IGludG8gdCBzZWxlY3QgaSBmcm9tIGdlbmVyYXRlX3Nlcmllcygx
LDEwMDAwMDAwKSBzKGkpOwoKY3JlYXRlIGluZGV4IGlkeF8xIG9uIHQgdXNpbmcgYnJpbiAo
YSkgd2l0aCAocGFnZXNfcGVyX3JhbmdlID0gNCk7CgpjcmVhdGUgaW5kZXggaWR4XzIgb24g
dCB1c2luZyBicmluIChhKTsKCmFuYWx5emUgdDsKClxkKwpcZGkrCgpleHBsYWluIGFuYWx5
emUgc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJvcCBpbmRleCBpZHhfMjsK
CmV4cGxhaW4gYW5hbHl6ZSBzZWxlY3QgKiBmcm9tIHQgd2hlcmUgYSA9IDEwMDA7Cg==
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproducer.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproducer.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0IChhIGludCwgYiBiaWdpbnQsIGMgdGV4dCkg
d2l0aCAoZmlsbGZhY3RvciA9IDUwKTsKCmluc2VydCBpbnRvIHQgc2VsZWN0IDEwMDAwMDAg
KiByYW5kb20oKSwgMTAwMDAwMCAqIHJhbmRvbSgpLCBtZDUoKDEwMDAwMDAgKiByYW5kb20o
KSk6OmludDo6dGV4dCkgZnJvbSBnZW5lcmF0ZV9zZXJpZXMoMSwxMDAwMDAwKSBzKGkpOwoK
c2VsZWN0IGNvdW50KCopIGZyb20gKHNlbGVjdCBkaXN0aW5jdCBiIGZyb20gdCB3aGVyZSBj
dGlkIDwgJygxMjgsMCknOjp0aWQpIGZvbzsKCmNyZWF0ZSBpbmRleCBibG9vbV9hX2lkeCBv
biB0IHVzaW5nIGJyaW4gKGEgaW50NF9ibG9vbV9vcHMobl9kaXN0aW5jdF9wZXJfcmFuZ2U9
NjAwMCkpOyAKCmFuYWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEg
PSAxMDAwOwoKY3JlYXRlIGluZGV4IGJyaW5faWR4IG9uIHQgdXNpbmcgYnJpbiAoYSk7CmFu
YWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJv
cCBpbmRleCBicmluX2lkeDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAx
MDAwOwo=
--------------D524CD37E77B36A2883A7617--
^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH 8/8] Consider selectivity in choose_bitmap_and
@ 2021-03-11 11:57 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 28+ messages in thread
From: Tomas Vondra @ 2021-03-11 11:57 UTC (permalink / raw)
---
src/backend/optimizer/path/indxpath.c | 38 ++++++++++++++++-----------
1 file changed, 23 insertions(+), 15 deletions(-)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index ff536e6b24..36b29c6c7b 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1437,6 +1437,7 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
foreach(l, paths)
{
Path *ipath = (Path *) lfirst(l);
+ bool duplicate = false;
pathinfo = classify_index_clause_usage(ipath, &clauselist);
@@ -1451,22 +1452,29 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
{
if (!pathinfoarray[i]->unclassifiable &&
bms_equal(pathinfo->clauseids, pathinfoarray[i]->clauseids))
- break;
- }
- if (i < npaths)
- {
- /* duplicate clauseids, keep the cheaper one */
- Cost ncost;
- Cost ocost;
- Selectivity nselec;
- Selectivity oselec;
-
- cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
- cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
- if (ncost < ocost)
- pathinfoarray[i] = pathinfo;
+ {
+ /* duplicate clauseids, keep the cheaper one
+ *
+ * XXX maybe this should just use path_usage_comparator?
+ */
+ Cost ncost;
+ Cost ocost;
+ Selectivity nselec;
+ Selectivity oselec;
+
+ cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
+ cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
+
+ if ((ncost < ocost) && (nselec < oselec))
+ {
+ pathinfoarray[i] = pathinfo;
+ duplicate = true;
+ break;
+ }
+ }
}
- else
+
+ if (!duplicate)
{
/* not duplicate clauseids, add to array */
pathinfoarray[npaths++] = pathinfo;
--
2.26.2
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproduce2.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproduce2.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0KGEgYmlnaW50KSB3aXRoIChmaWxsZmFjdG9y
ID0gMjUpOwoKaW5zZXJ0IGludG8gdCBzZWxlY3QgaSBmcm9tIGdlbmVyYXRlX3Nlcmllcygx
LDEwMDAwMDAwKSBzKGkpOwoKY3JlYXRlIGluZGV4IGlkeF8xIG9uIHQgdXNpbmcgYnJpbiAo
YSkgd2l0aCAocGFnZXNfcGVyX3JhbmdlID0gNCk7CgpjcmVhdGUgaW5kZXggaWR4XzIgb24g
dCB1c2luZyBicmluIChhKTsKCmFuYWx5emUgdDsKClxkKwpcZGkrCgpleHBsYWluIGFuYWx5
emUgc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJvcCBpbmRleCBpZHhfMjsK
CmV4cGxhaW4gYW5hbHl6ZSBzZWxlY3QgKiBmcm9tIHQgd2hlcmUgYSA9IDEwMDA7Cg==
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproducer.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproducer.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0IChhIGludCwgYiBiaWdpbnQsIGMgdGV4dCkg
d2l0aCAoZmlsbGZhY3RvciA9IDUwKTsKCmluc2VydCBpbnRvIHQgc2VsZWN0IDEwMDAwMDAg
KiByYW5kb20oKSwgMTAwMDAwMCAqIHJhbmRvbSgpLCBtZDUoKDEwMDAwMDAgKiByYW5kb20o
KSk6OmludDo6dGV4dCkgZnJvbSBnZW5lcmF0ZV9zZXJpZXMoMSwxMDAwMDAwKSBzKGkpOwoK
c2VsZWN0IGNvdW50KCopIGZyb20gKHNlbGVjdCBkaXN0aW5jdCBiIGZyb20gdCB3aGVyZSBj
dGlkIDwgJygxMjgsMCknOjp0aWQpIGZvbzsKCmNyZWF0ZSBpbmRleCBibG9vbV9hX2lkeCBv
biB0IHVzaW5nIGJyaW4gKGEgaW50NF9ibG9vbV9vcHMobl9kaXN0aW5jdF9wZXJfcmFuZ2U9
NjAwMCkpOyAKCmFuYWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEg
PSAxMDAwOwoKY3JlYXRlIGluZGV4IGJyaW5faWR4IG9uIHQgdXNpbmcgYnJpbiAoYSk7CmFu
YWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJv
cCBpbmRleCBicmluX2lkeDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAx
MDAwOwo=
--------------D524CD37E77B36A2883A7617--
^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH 8/8] Consider selectivity in choose_bitmap_and
@ 2021-03-11 11:57 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 28+ messages in thread
From: Tomas Vondra @ 2021-03-11 11:57 UTC (permalink / raw)
---
src/backend/optimizer/path/indxpath.c | 38 ++++++++++++++++-----------
1 file changed, 23 insertions(+), 15 deletions(-)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index ff536e6b24..36b29c6c7b 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1437,6 +1437,7 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
foreach(l, paths)
{
Path *ipath = (Path *) lfirst(l);
+ bool duplicate = false;
pathinfo = classify_index_clause_usage(ipath, &clauselist);
@@ -1451,22 +1452,29 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
{
if (!pathinfoarray[i]->unclassifiable &&
bms_equal(pathinfo->clauseids, pathinfoarray[i]->clauseids))
- break;
- }
- if (i < npaths)
- {
- /* duplicate clauseids, keep the cheaper one */
- Cost ncost;
- Cost ocost;
- Selectivity nselec;
- Selectivity oselec;
-
- cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
- cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
- if (ncost < ocost)
- pathinfoarray[i] = pathinfo;
+ {
+ /* duplicate clauseids, keep the cheaper one
+ *
+ * XXX maybe this should just use path_usage_comparator?
+ */
+ Cost ncost;
+ Cost ocost;
+ Selectivity nselec;
+ Selectivity oselec;
+
+ cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
+ cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
+
+ if ((ncost < ocost) && (nselec < oselec))
+ {
+ pathinfoarray[i] = pathinfo;
+ duplicate = true;
+ break;
+ }
+ }
}
- else
+
+ if (!duplicate)
{
/* not duplicate clauseids, add to array */
pathinfoarray[npaths++] = pathinfo;
--
2.26.2
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproduce2.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproduce2.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0KGEgYmlnaW50KSB3aXRoIChmaWxsZmFjdG9y
ID0gMjUpOwoKaW5zZXJ0IGludG8gdCBzZWxlY3QgaSBmcm9tIGdlbmVyYXRlX3Nlcmllcygx
LDEwMDAwMDAwKSBzKGkpOwoKY3JlYXRlIGluZGV4IGlkeF8xIG9uIHQgdXNpbmcgYnJpbiAo
YSkgd2l0aCAocGFnZXNfcGVyX3JhbmdlID0gNCk7CgpjcmVhdGUgaW5kZXggaWR4XzIgb24g
dCB1c2luZyBicmluIChhKTsKCmFuYWx5emUgdDsKClxkKwpcZGkrCgpleHBsYWluIGFuYWx5
emUgc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJvcCBpbmRleCBpZHhfMjsK
CmV4cGxhaW4gYW5hbHl6ZSBzZWxlY3QgKiBmcm9tIHQgd2hlcmUgYSA9IDEwMDA7Cg==
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproducer.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproducer.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0IChhIGludCwgYiBiaWdpbnQsIGMgdGV4dCkg
d2l0aCAoZmlsbGZhY3RvciA9IDUwKTsKCmluc2VydCBpbnRvIHQgc2VsZWN0IDEwMDAwMDAg
KiByYW5kb20oKSwgMTAwMDAwMCAqIHJhbmRvbSgpLCBtZDUoKDEwMDAwMDAgKiByYW5kb20o
KSk6OmludDo6dGV4dCkgZnJvbSBnZW5lcmF0ZV9zZXJpZXMoMSwxMDAwMDAwKSBzKGkpOwoK
c2VsZWN0IGNvdW50KCopIGZyb20gKHNlbGVjdCBkaXN0aW5jdCBiIGZyb20gdCB3aGVyZSBj
dGlkIDwgJygxMjgsMCknOjp0aWQpIGZvbzsKCmNyZWF0ZSBpbmRleCBibG9vbV9hX2lkeCBv
biB0IHVzaW5nIGJyaW4gKGEgaW50NF9ibG9vbV9vcHMobl9kaXN0aW5jdF9wZXJfcmFuZ2U9
NjAwMCkpOyAKCmFuYWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEg
PSAxMDAwOwoKY3JlYXRlIGluZGV4IGJyaW5faWR4IG9uIHQgdXNpbmcgYnJpbiAoYSk7CmFu
YWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJv
cCBpbmRleCBicmluX2lkeDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAx
MDAwOwo=
--------------D524CD37E77B36A2883A7617--
^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH 8/8] Consider selectivity in choose_bitmap_and
@ 2021-03-11 11:57 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 28+ messages in thread
From: Tomas Vondra @ 2021-03-11 11:57 UTC (permalink / raw)
---
src/backend/optimizer/path/indxpath.c | 38 ++++++++++++++++-----------
1 file changed, 23 insertions(+), 15 deletions(-)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index ff536e6b24..36b29c6c7b 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1437,6 +1437,7 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
foreach(l, paths)
{
Path *ipath = (Path *) lfirst(l);
+ bool duplicate = false;
pathinfo = classify_index_clause_usage(ipath, &clauselist);
@@ -1451,22 +1452,29 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
{
if (!pathinfoarray[i]->unclassifiable &&
bms_equal(pathinfo->clauseids, pathinfoarray[i]->clauseids))
- break;
- }
- if (i < npaths)
- {
- /* duplicate clauseids, keep the cheaper one */
- Cost ncost;
- Cost ocost;
- Selectivity nselec;
- Selectivity oselec;
-
- cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
- cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
- if (ncost < ocost)
- pathinfoarray[i] = pathinfo;
+ {
+ /* duplicate clauseids, keep the cheaper one
+ *
+ * XXX maybe this should just use path_usage_comparator?
+ */
+ Cost ncost;
+ Cost ocost;
+ Selectivity nselec;
+ Selectivity oselec;
+
+ cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
+ cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
+
+ if ((ncost < ocost) && (nselec < oselec))
+ {
+ pathinfoarray[i] = pathinfo;
+ duplicate = true;
+ break;
+ }
+ }
}
- else
+
+ if (!duplicate)
{
/* not duplicate clauseids, add to array */
pathinfoarray[npaths++] = pathinfo;
--
2.26.2
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproduce2.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproduce2.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0KGEgYmlnaW50KSB3aXRoIChmaWxsZmFjdG9y
ID0gMjUpOwoKaW5zZXJ0IGludG8gdCBzZWxlY3QgaSBmcm9tIGdlbmVyYXRlX3Nlcmllcygx
LDEwMDAwMDAwKSBzKGkpOwoKY3JlYXRlIGluZGV4IGlkeF8xIG9uIHQgdXNpbmcgYnJpbiAo
YSkgd2l0aCAocGFnZXNfcGVyX3JhbmdlID0gNCk7CgpjcmVhdGUgaW5kZXggaWR4XzIgb24g
dCB1c2luZyBicmluIChhKTsKCmFuYWx5emUgdDsKClxkKwpcZGkrCgpleHBsYWluIGFuYWx5
emUgc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJvcCBpbmRleCBpZHhfMjsK
CmV4cGxhaW4gYW5hbHl6ZSBzZWxlY3QgKiBmcm9tIHQgd2hlcmUgYSA9IDEwMDA7Cg==
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproducer.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproducer.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0IChhIGludCwgYiBiaWdpbnQsIGMgdGV4dCkg
d2l0aCAoZmlsbGZhY3RvciA9IDUwKTsKCmluc2VydCBpbnRvIHQgc2VsZWN0IDEwMDAwMDAg
KiByYW5kb20oKSwgMTAwMDAwMCAqIHJhbmRvbSgpLCBtZDUoKDEwMDAwMDAgKiByYW5kb20o
KSk6OmludDo6dGV4dCkgZnJvbSBnZW5lcmF0ZV9zZXJpZXMoMSwxMDAwMDAwKSBzKGkpOwoK
c2VsZWN0IGNvdW50KCopIGZyb20gKHNlbGVjdCBkaXN0aW5jdCBiIGZyb20gdCB3aGVyZSBj
dGlkIDwgJygxMjgsMCknOjp0aWQpIGZvbzsKCmNyZWF0ZSBpbmRleCBibG9vbV9hX2lkeCBv
biB0IHVzaW5nIGJyaW4gKGEgaW50NF9ibG9vbV9vcHMobl9kaXN0aW5jdF9wZXJfcmFuZ2U9
NjAwMCkpOyAKCmFuYWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEg
PSAxMDAwOwoKY3JlYXRlIGluZGV4IGJyaW5faWR4IG9uIHQgdXNpbmcgYnJpbiAoYSk7CmFu
YWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJv
cCBpbmRleCBicmluX2lkeDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAx
MDAwOwo=
--------------D524CD37E77B36A2883A7617--
^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH 8/8] Consider selectivity in choose_bitmap_and
@ 2021-03-11 11:57 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 28+ messages in thread
From: Tomas Vondra @ 2021-03-11 11:57 UTC (permalink / raw)
---
src/backend/optimizer/path/indxpath.c | 38 ++++++++++++++++-----------
1 file changed, 23 insertions(+), 15 deletions(-)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index ff536e6b24..36b29c6c7b 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1437,6 +1437,7 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
foreach(l, paths)
{
Path *ipath = (Path *) lfirst(l);
+ bool duplicate = false;
pathinfo = classify_index_clause_usage(ipath, &clauselist);
@@ -1451,22 +1452,29 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
{
if (!pathinfoarray[i]->unclassifiable &&
bms_equal(pathinfo->clauseids, pathinfoarray[i]->clauseids))
- break;
- }
- if (i < npaths)
- {
- /* duplicate clauseids, keep the cheaper one */
- Cost ncost;
- Cost ocost;
- Selectivity nselec;
- Selectivity oselec;
-
- cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
- cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
- if (ncost < ocost)
- pathinfoarray[i] = pathinfo;
+ {
+ /* duplicate clauseids, keep the cheaper one
+ *
+ * XXX maybe this should just use path_usage_comparator?
+ */
+ Cost ncost;
+ Cost ocost;
+ Selectivity nselec;
+ Selectivity oselec;
+
+ cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
+ cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
+
+ if ((ncost < ocost) && (nselec < oselec))
+ {
+ pathinfoarray[i] = pathinfo;
+ duplicate = true;
+ break;
+ }
+ }
}
- else
+
+ if (!duplicate)
{
/* not duplicate clauseids, add to array */
pathinfoarray[npaths++] = pathinfo;
--
2.26.2
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproduce2.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproduce2.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0KGEgYmlnaW50KSB3aXRoIChmaWxsZmFjdG9y
ID0gMjUpOwoKaW5zZXJ0IGludG8gdCBzZWxlY3QgaSBmcm9tIGdlbmVyYXRlX3Nlcmllcygx
LDEwMDAwMDAwKSBzKGkpOwoKY3JlYXRlIGluZGV4IGlkeF8xIG9uIHQgdXNpbmcgYnJpbiAo
YSkgd2l0aCAocGFnZXNfcGVyX3JhbmdlID0gNCk7CgpjcmVhdGUgaW5kZXggaWR4XzIgb24g
dCB1c2luZyBicmluIChhKTsKCmFuYWx5emUgdDsKClxkKwpcZGkrCgpleHBsYWluIGFuYWx5
emUgc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJvcCBpbmRleCBpZHhfMjsK
CmV4cGxhaW4gYW5hbHl6ZSBzZWxlY3QgKiBmcm9tIHQgd2hlcmUgYSA9IDEwMDA7Cg==
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproducer.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproducer.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0IChhIGludCwgYiBiaWdpbnQsIGMgdGV4dCkg
d2l0aCAoZmlsbGZhY3RvciA9IDUwKTsKCmluc2VydCBpbnRvIHQgc2VsZWN0IDEwMDAwMDAg
KiByYW5kb20oKSwgMTAwMDAwMCAqIHJhbmRvbSgpLCBtZDUoKDEwMDAwMDAgKiByYW5kb20o
KSk6OmludDo6dGV4dCkgZnJvbSBnZW5lcmF0ZV9zZXJpZXMoMSwxMDAwMDAwKSBzKGkpOwoK
c2VsZWN0IGNvdW50KCopIGZyb20gKHNlbGVjdCBkaXN0aW5jdCBiIGZyb20gdCB3aGVyZSBj
dGlkIDwgJygxMjgsMCknOjp0aWQpIGZvbzsKCmNyZWF0ZSBpbmRleCBibG9vbV9hX2lkeCBv
biB0IHVzaW5nIGJyaW4gKGEgaW50NF9ibG9vbV9vcHMobl9kaXN0aW5jdF9wZXJfcmFuZ2U9
NjAwMCkpOyAKCmFuYWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEg
PSAxMDAwOwoKY3JlYXRlIGluZGV4IGJyaW5faWR4IG9uIHQgdXNpbmcgYnJpbiAoYSk7CmFu
YWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJv
cCBpbmRleCBicmluX2lkeDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAx
MDAwOwo=
--------------D524CD37E77B36A2883A7617--
^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH 8/8] Consider selectivity in choose_bitmap_and
@ 2021-03-11 11:57 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 28+ messages in thread
From: Tomas Vondra @ 2021-03-11 11:57 UTC (permalink / raw)
---
src/backend/optimizer/path/indxpath.c | 38 ++++++++++++++++-----------
1 file changed, 23 insertions(+), 15 deletions(-)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index ff536e6b24..36b29c6c7b 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1437,6 +1437,7 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
foreach(l, paths)
{
Path *ipath = (Path *) lfirst(l);
+ bool duplicate = false;
pathinfo = classify_index_clause_usage(ipath, &clauselist);
@@ -1451,22 +1452,29 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
{
if (!pathinfoarray[i]->unclassifiable &&
bms_equal(pathinfo->clauseids, pathinfoarray[i]->clauseids))
- break;
- }
- if (i < npaths)
- {
- /* duplicate clauseids, keep the cheaper one */
- Cost ncost;
- Cost ocost;
- Selectivity nselec;
- Selectivity oselec;
-
- cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
- cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
- if (ncost < ocost)
- pathinfoarray[i] = pathinfo;
+ {
+ /* duplicate clauseids, keep the cheaper one
+ *
+ * XXX maybe this should just use path_usage_comparator?
+ */
+ Cost ncost;
+ Cost ocost;
+ Selectivity nselec;
+ Selectivity oselec;
+
+ cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
+ cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
+
+ if ((ncost < ocost) && (nselec < oselec))
+ {
+ pathinfoarray[i] = pathinfo;
+ duplicate = true;
+ break;
+ }
+ }
}
- else
+
+ if (!duplicate)
{
/* not duplicate clauseids, add to array */
pathinfoarray[npaths++] = pathinfo;
--
2.26.2
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproduce2.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproduce2.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0KGEgYmlnaW50KSB3aXRoIChmaWxsZmFjdG9y
ID0gMjUpOwoKaW5zZXJ0IGludG8gdCBzZWxlY3QgaSBmcm9tIGdlbmVyYXRlX3Nlcmllcygx
LDEwMDAwMDAwKSBzKGkpOwoKY3JlYXRlIGluZGV4IGlkeF8xIG9uIHQgdXNpbmcgYnJpbiAo
YSkgd2l0aCAocGFnZXNfcGVyX3JhbmdlID0gNCk7CgpjcmVhdGUgaW5kZXggaWR4XzIgb24g
dCB1c2luZyBicmluIChhKTsKCmFuYWx5emUgdDsKClxkKwpcZGkrCgpleHBsYWluIGFuYWx5
emUgc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJvcCBpbmRleCBpZHhfMjsK
CmV4cGxhaW4gYW5hbHl6ZSBzZWxlY3QgKiBmcm9tIHQgd2hlcmUgYSA9IDEwMDA7Cg==
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproducer.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproducer.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0IChhIGludCwgYiBiaWdpbnQsIGMgdGV4dCkg
d2l0aCAoZmlsbGZhY3RvciA9IDUwKTsKCmluc2VydCBpbnRvIHQgc2VsZWN0IDEwMDAwMDAg
KiByYW5kb20oKSwgMTAwMDAwMCAqIHJhbmRvbSgpLCBtZDUoKDEwMDAwMDAgKiByYW5kb20o
KSk6OmludDo6dGV4dCkgZnJvbSBnZW5lcmF0ZV9zZXJpZXMoMSwxMDAwMDAwKSBzKGkpOwoK
c2VsZWN0IGNvdW50KCopIGZyb20gKHNlbGVjdCBkaXN0aW5jdCBiIGZyb20gdCB3aGVyZSBj
dGlkIDwgJygxMjgsMCknOjp0aWQpIGZvbzsKCmNyZWF0ZSBpbmRleCBibG9vbV9hX2lkeCBv
biB0IHVzaW5nIGJyaW4gKGEgaW50NF9ibG9vbV9vcHMobl9kaXN0aW5jdF9wZXJfcmFuZ2U9
NjAwMCkpOyAKCmFuYWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEg
PSAxMDAwOwoKY3JlYXRlIGluZGV4IGJyaW5faWR4IG9uIHQgdXNpbmcgYnJpbiAoYSk7CmFu
YWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJv
cCBpbmRleCBicmluX2lkeDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAx
MDAwOwo=
--------------D524CD37E77B36A2883A7617--
^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH 8/8] Consider selectivity in choose_bitmap_and
@ 2021-03-11 11:57 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 28+ messages in thread
From: Tomas Vondra @ 2021-03-11 11:57 UTC (permalink / raw)
---
src/backend/optimizer/path/indxpath.c | 38 ++++++++++++++++-----------
1 file changed, 23 insertions(+), 15 deletions(-)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index ff536e6b24..36b29c6c7b 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1437,6 +1437,7 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
foreach(l, paths)
{
Path *ipath = (Path *) lfirst(l);
+ bool duplicate = false;
pathinfo = classify_index_clause_usage(ipath, &clauselist);
@@ -1451,22 +1452,29 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
{
if (!pathinfoarray[i]->unclassifiable &&
bms_equal(pathinfo->clauseids, pathinfoarray[i]->clauseids))
- break;
- }
- if (i < npaths)
- {
- /* duplicate clauseids, keep the cheaper one */
- Cost ncost;
- Cost ocost;
- Selectivity nselec;
- Selectivity oselec;
-
- cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
- cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
- if (ncost < ocost)
- pathinfoarray[i] = pathinfo;
+ {
+ /* duplicate clauseids, keep the cheaper one
+ *
+ * XXX maybe this should just use path_usage_comparator?
+ */
+ Cost ncost;
+ Cost ocost;
+ Selectivity nselec;
+ Selectivity oselec;
+
+ cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
+ cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
+
+ if ((ncost < ocost) && (nselec < oselec))
+ {
+ pathinfoarray[i] = pathinfo;
+ duplicate = true;
+ break;
+ }
+ }
}
- else
+
+ if (!duplicate)
{
/* not duplicate clauseids, add to array */
pathinfoarray[npaths++] = pathinfo;
--
2.26.2
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproduce2.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproduce2.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0KGEgYmlnaW50KSB3aXRoIChmaWxsZmFjdG9y
ID0gMjUpOwoKaW5zZXJ0IGludG8gdCBzZWxlY3QgaSBmcm9tIGdlbmVyYXRlX3Nlcmllcygx
LDEwMDAwMDAwKSBzKGkpOwoKY3JlYXRlIGluZGV4IGlkeF8xIG9uIHQgdXNpbmcgYnJpbiAo
YSkgd2l0aCAocGFnZXNfcGVyX3JhbmdlID0gNCk7CgpjcmVhdGUgaW5kZXggaWR4XzIgb24g
dCB1c2luZyBicmluIChhKTsKCmFuYWx5emUgdDsKClxkKwpcZGkrCgpleHBsYWluIGFuYWx5
emUgc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJvcCBpbmRleCBpZHhfMjsK
CmV4cGxhaW4gYW5hbHl6ZSBzZWxlY3QgKiBmcm9tIHQgd2hlcmUgYSA9IDEwMDA7Cg==
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproducer.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproducer.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0IChhIGludCwgYiBiaWdpbnQsIGMgdGV4dCkg
d2l0aCAoZmlsbGZhY3RvciA9IDUwKTsKCmluc2VydCBpbnRvIHQgc2VsZWN0IDEwMDAwMDAg
KiByYW5kb20oKSwgMTAwMDAwMCAqIHJhbmRvbSgpLCBtZDUoKDEwMDAwMDAgKiByYW5kb20o
KSk6OmludDo6dGV4dCkgZnJvbSBnZW5lcmF0ZV9zZXJpZXMoMSwxMDAwMDAwKSBzKGkpOwoK
c2VsZWN0IGNvdW50KCopIGZyb20gKHNlbGVjdCBkaXN0aW5jdCBiIGZyb20gdCB3aGVyZSBj
dGlkIDwgJygxMjgsMCknOjp0aWQpIGZvbzsKCmNyZWF0ZSBpbmRleCBibG9vbV9hX2lkeCBv
biB0IHVzaW5nIGJyaW4gKGEgaW50NF9ibG9vbV9vcHMobl9kaXN0aW5jdF9wZXJfcmFuZ2U9
NjAwMCkpOyAKCmFuYWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEg
PSAxMDAwOwoKY3JlYXRlIGluZGV4IGJyaW5faWR4IG9uIHQgdXNpbmcgYnJpbiAoYSk7CmFu
YWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJv
cCBpbmRleCBicmluX2lkeDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAx
MDAwOwo=
--------------D524CD37E77B36A2883A7617--
^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH 8/8] Consider selectivity in choose_bitmap_and
@ 2021-03-11 11:57 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 28+ messages in thread
From: Tomas Vondra @ 2021-03-11 11:57 UTC (permalink / raw)
---
src/backend/optimizer/path/indxpath.c | 38 ++++++++++++++++-----------
1 file changed, 23 insertions(+), 15 deletions(-)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index ff536e6b24..36b29c6c7b 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1437,6 +1437,7 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
foreach(l, paths)
{
Path *ipath = (Path *) lfirst(l);
+ bool duplicate = false;
pathinfo = classify_index_clause_usage(ipath, &clauselist);
@@ -1451,22 +1452,29 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
{
if (!pathinfoarray[i]->unclassifiable &&
bms_equal(pathinfo->clauseids, pathinfoarray[i]->clauseids))
- break;
- }
- if (i < npaths)
- {
- /* duplicate clauseids, keep the cheaper one */
- Cost ncost;
- Cost ocost;
- Selectivity nselec;
- Selectivity oselec;
-
- cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
- cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
- if (ncost < ocost)
- pathinfoarray[i] = pathinfo;
+ {
+ /* duplicate clauseids, keep the cheaper one
+ *
+ * XXX maybe this should just use path_usage_comparator?
+ */
+ Cost ncost;
+ Cost ocost;
+ Selectivity nselec;
+ Selectivity oselec;
+
+ cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
+ cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
+
+ if ((ncost < ocost) && (nselec < oselec))
+ {
+ pathinfoarray[i] = pathinfo;
+ duplicate = true;
+ break;
+ }
+ }
}
- else
+
+ if (!duplicate)
{
/* not duplicate clauseids, add to array */
pathinfoarray[npaths++] = pathinfo;
--
2.26.2
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproduce2.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproduce2.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0KGEgYmlnaW50KSB3aXRoIChmaWxsZmFjdG9y
ID0gMjUpOwoKaW5zZXJ0IGludG8gdCBzZWxlY3QgaSBmcm9tIGdlbmVyYXRlX3Nlcmllcygx
LDEwMDAwMDAwKSBzKGkpOwoKY3JlYXRlIGluZGV4IGlkeF8xIG9uIHQgdXNpbmcgYnJpbiAo
YSkgd2l0aCAocGFnZXNfcGVyX3JhbmdlID0gNCk7CgpjcmVhdGUgaW5kZXggaWR4XzIgb24g
dCB1c2luZyBicmluIChhKTsKCmFuYWx5emUgdDsKClxkKwpcZGkrCgpleHBsYWluIGFuYWx5
emUgc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJvcCBpbmRleCBpZHhfMjsK
CmV4cGxhaW4gYW5hbHl6ZSBzZWxlY3QgKiBmcm9tIHQgd2hlcmUgYSA9IDEwMDA7Cg==
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproducer.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproducer.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0IChhIGludCwgYiBiaWdpbnQsIGMgdGV4dCkg
d2l0aCAoZmlsbGZhY3RvciA9IDUwKTsKCmluc2VydCBpbnRvIHQgc2VsZWN0IDEwMDAwMDAg
KiByYW5kb20oKSwgMTAwMDAwMCAqIHJhbmRvbSgpLCBtZDUoKDEwMDAwMDAgKiByYW5kb20o
KSk6OmludDo6dGV4dCkgZnJvbSBnZW5lcmF0ZV9zZXJpZXMoMSwxMDAwMDAwKSBzKGkpOwoK
c2VsZWN0IGNvdW50KCopIGZyb20gKHNlbGVjdCBkaXN0aW5jdCBiIGZyb20gdCB3aGVyZSBj
dGlkIDwgJygxMjgsMCknOjp0aWQpIGZvbzsKCmNyZWF0ZSBpbmRleCBibG9vbV9hX2lkeCBv
biB0IHVzaW5nIGJyaW4gKGEgaW50NF9ibG9vbV9vcHMobl9kaXN0aW5jdF9wZXJfcmFuZ2U9
NjAwMCkpOyAKCmFuYWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEg
PSAxMDAwOwoKY3JlYXRlIGluZGV4IGJyaW5faWR4IG9uIHQgdXNpbmcgYnJpbiAoYSk7CmFu
YWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJv
cCBpbmRleCBicmluX2lkeDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAx
MDAwOwo=
--------------D524CD37E77B36A2883A7617--
^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH 8/8] Consider selectivity in choose_bitmap_and
@ 2021-03-11 11:57 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 28+ messages in thread
From: Tomas Vondra @ 2021-03-11 11:57 UTC (permalink / raw)
---
src/backend/optimizer/path/indxpath.c | 38 ++++++++++++++++-----------
1 file changed, 23 insertions(+), 15 deletions(-)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index ff536e6b24..36b29c6c7b 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1437,6 +1437,7 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
foreach(l, paths)
{
Path *ipath = (Path *) lfirst(l);
+ bool duplicate = false;
pathinfo = classify_index_clause_usage(ipath, &clauselist);
@@ -1451,22 +1452,29 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
{
if (!pathinfoarray[i]->unclassifiable &&
bms_equal(pathinfo->clauseids, pathinfoarray[i]->clauseids))
- break;
- }
- if (i < npaths)
- {
- /* duplicate clauseids, keep the cheaper one */
- Cost ncost;
- Cost ocost;
- Selectivity nselec;
- Selectivity oselec;
-
- cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
- cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
- if (ncost < ocost)
- pathinfoarray[i] = pathinfo;
+ {
+ /* duplicate clauseids, keep the cheaper one
+ *
+ * XXX maybe this should just use path_usage_comparator?
+ */
+ Cost ncost;
+ Cost ocost;
+ Selectivity nselec;
+ Selectivity oselec;
+
+ cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
+ cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
+
+ if ((ncost < ocost) && (nselec < oselec))
+ {
+ pathinfoarray[i] = pathinfo;
+ duplicate = true;
+ break;
+ }
+ }
}
- else
+
+ if (!duplicate)
{
/* not duplicate clauseids, add to array */
pathinfoarray[npaths++] = pathinfo;
--
2.26.2
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproduce2.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproduce2.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0KGEgYmlnaW50KSB3aXRoIChmaWxsZmFjdG9y
ID0gMjUpOwoKaW5zZXJ0IGludG8gdCBzZWxlY3QgaSBmcm9tIGdlbmVyYXRlX3Nlcmllcygx
LDEwMDAwMDAwKSBzKGkpOwoKY3JlYXRlIGluZGV4IGlkeF8xIG9uIHQgdXNpbmcgYnJpbiAo
YSkgd2l0aCAocGFnZXNfcGVyX3JhbmdlID0gNCk7CgpjcmVhdGUgaW5kZXggaWR4XzIgb24g
dCB1c2luZyBicmluIChhKTsKCmFuYWx5emUgdDsKClxkKwpcZGkrCgpleHBsYWluIGFuYWx5
emUgc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJvcCBpbmRleCBpZHhfMjsK
CmV4cGxhaW4gYW5hbHl6ZSBzZWxlY3QgKiBmcm9tIHQgd2hlcmUgYSA9IDEwMDA7Cg==
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproducer.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproducer.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0IChhIGludCwgYiBiaWdpbnQsIGMgdGV4dCkg
d2l0aCAoZmlsbGZhY3RvciA9IDUwKTsKCmluc2VydCBpbnRvIHQgc2VsZWN0IDEwMDAwMDAg
KiByYW5kb20oKSwgMTAwMDAwMCAqIHJhbmRvbSgpLCBtZDUoKDEwMDAwMDAgKiByYW5kb20o
KSk6OmludDo6dGV4dCkgZnJvbSBnZW5lcmF0ZV9zZXJpZXMoMSwxMDAwMDAwKSBzKGkpOwoK
c2VsZWN0IGNvdW50KCopIGZyb20gKHNlbGVjdCBkaXN0aW5jdCBiIGZyb20gdCB3aGVyZSBj
dGlkIDwgJygxMjgsMCknOjp0aWQpIGZvbzsKCmNyZWF0ZSBpbmRleCBibG9vbV9hX2lkeCBv
biB0IHVzaW5nIGJyaW4gKGEgaW50NF9ibG9vbV9vcHMobl9kaXN0aW5jdF9wZXJfcmFuZ2U9
NjAwMCkpOyAKCmFuYWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEg
PSAxMDAwOwoKY3JlYXRlIGluZGV4IGJyaW5faWR4IG9uIHQgdXNpbmcgYnJpbiAoYSk7CmFu
YWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJv
cCBpbmRleCBicmluX2lkeDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAx
MDAwOwo=
--------------D524CD37E77B36A2883A7617--
^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH 8/8] Consider selectivity in choose_bitmap_and
@ 2021-03-11 11:57 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 28+ messages in thread
From: Tomas Vondra @ 2021-03-11 11:57 UTC (permalink / raw)
---
src/backend/optimizer/path/indxpath.c | 38 ++++++++++++++++-----------
1 file changed, 23 insertions(+), 15 deletions(-)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index ff536e6b24..36b29c6c7b 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1437,6 +1437,7 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
foreach(l, paths)
{
Path *ipath = (Path *) lfirst(l);
+ bool duplicate = false;
pathinfo = classify_index_clause_usage(ipath, &clauselist);
@@ -1451,22 +1452,29 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
{
if (!pathinfoarray[i]->unclassifiable &&
bms_equal(pathinfo->clauseids, pathinfoarray[i]->clauseids))
- break;
- }
- if (i < npaths)
- {
- /* duplicate clauseids, keep the cheaper one */
- Cost ncost;
- Cost ocost;
- Selectivity nselec;
- Selectivity oselec;
-
- cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
- cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
- if (ncost < ocost)
- pathinfoarray[i] = pathinfo;
+ {
+ /* duplicate clauseids, keep the cheaper one
+ *
+ * XXX maybe this should just use path_usage_comparator?
+ */
+ Cost ncost;
+ Cost ocost;
+ Selectivity nselec;
+ Selectivity oselec;
+
+ cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
+ cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
+
+ if ((ncost < ocost) && (nselec < oselec))
+ {
+ pathinfoarray[i] = pathinfo;
+ duplicate = true;
+ break;
+ }
+ }
}
- else
+
+ if (!duplicate)
{
/* not duplicate clauseids, add to array */
pathinfoarray[npaths++] = pathinfo;
--
2.26.2
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproduce2.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproduce2.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0KGEgYmlnaW50KSB3aXRoIChmaWxsZmFjdG9y
ID0gMjUpOwoKaW5zZXJ0IGludG8gdCBzZWxlY3QgaSBmcm9tIGdlbmVyYXRlX3Nlcmllcygx
LDEwMDAwMDAwKSBzKGkpOwoKY3JlYXRlIGluZGV4IGlkeF8xIG9uIHQgdXNpbmcgYnJpbiAo
YSkgd2l0aCAocGFnZXNfcGVyX3JhbmdlID0gNCk7CgpjcmVhdGUgaW5kZXggaWR4XzIgb24g
dCB1c2luZyBicmluIChhKTsKCmFuYWx5emUgdDsKClxkKwpcZGkrCgpleHBsYWluIGFuYWx5
emUgc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJvcCBpbmRleCBpZHhfMjsK
CmV4cGxhaW4gYW5hbHl6ZSBzZWxlY3QgKiBmcm9tIHQgd2hlcmUgYSA9IDEwMDA7Cg==
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproducer.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproducer.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0IChhIGludCwgYiBiaWdpbnQsIGMgdGV4dCkg
d2l0aCAoZmlsbGZhY3RvciA9IDUwKTsKCmluc2VydCBpbnRvIHQgc2VsZWN0IDEwMDAwMDAg
KiByYW5kb20oKSwgMTAwMDAwMCAqIHJhbmRvbSgpLCBtZDUoKDEwMDAwMDAgKiByYW5kb20o
KSk6OmludDo6dGV4dCkgZnJvbSBnZW5lcmF0ZV9zZXJpZXMoMSwxMDAwMDAwKSBzKGkpOwoK
c2VsZWN0IGNvdW50KCopIGZyb20gKHNlbGVjdCBkaXN0aW5jdCBiIGZyb20gdCB3aGVyZSBj
dGlkIDwgJygxMjgsMCknOjp0aWQpIGZvbzsKCmNyZWF0ZSBpbmRleCBibG9vbV9hX2lkeCBv
biB0IHVzaW5nIGJyaW4gKGEgaW50NF9ibG9vbV9vcHMobl9kaXN0aW5jdF9wZXJfcmFuZ2U9
NjAwMCkpOyAKCmFuYWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEg
PSAxMDAwOwoKY3JlYXRlIGluZGV4IGJyaW5faWR4IG9uIHQgdXNpbmcgYnJpbiAoYSk7CmFu
YWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJv
cCBpbmRleCBicmluX2lkeDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAx
MDAwOwo=
--------------D524CD37E77B36A2883A7617--
^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH 8/8] Consider selectivity in choose_bitmap_and
@ 2021-03-11 11:57 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 28+ messages in thread
From: Tomas Vondra @ 2021-03-11 11:57 UTC (permalink / raw)
---
src/backend/optimizer/path/indxpath.c | 38 ++++++++++++++++-----------
1 file changed, 23 insertions(+), 15 deletions(-)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index ff536e6b24..36b29c6c7b 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1437,6 +1437,7 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
foreach(l, paths)
{
Path *ipath = (Path *) lfirst(l);
+ bool duplicate = false;
pathinfo = classify_index_clause_usage(ipath, &clauselist);
@@ -1451,22 +1452,29 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
{
if (!pathinfoarray[i]->unclassifiable &&
bms_equal(pathinfo->clauseids, pathinfoarray[i]->clauseids))
- break;
- }
- if (i < npaths)
- {
- /* duplicate clauseids, keep the cheaper one */
- Cost ncost;
- Cost ocost;
- Selectivity nselec;
- Selectivity oselec;
-
- cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
- cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
- if (ncost < ocost)
- pathinfoarray[i] = pathinfo;
+ {
+ /* duplicate clauseids, keep the cheaper one
+ *
+ * XXX maybe this should just use path_usage_comparator?
+ */
+ Cost ncost;
+ Cost ocost;
+ Selectivity nselec;
+ Selectivity oselec;
+
+ cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
+ cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
+
+ if ((ncost < ocost) && (nselec < oselec))
+ {
+ pathinfoarray[i] = pathinfo;
+ duplicate = true;
+ break;
+ }
+ }
}
- else
+
+ if (!duplicate)
{
/* not duplicate clauseids, add to array */
pathinfoarray[npaths++] = pathinfo;
--
2.26.2
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproduce2.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproduce2.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0KGEgYmlnaW50KSB3aXRoIChmaWxsZmFjdG9y
ID0gMjUpOwoKaW5zZXJ0IGludG8gdCBzZWxlY3QgaSBmcm9tIGdlbmVyYXRlX3Nlcmllcygx
LDEwMDAwMDAwKSBzKGkpOwoKY3JlYXRlIGluZGV4IGlkeF8xIG9uIHQgdXNpbmcgYnJpbiAo
YSkgd2l0aCAocGFnZXNfcGVyX3JhbmdlID0gNCk7CgpjcmVhdGUgaW5kZXggaWR4XzIgb24g
dCB1c2luZyBicmluIChhKTsKCmFuYWx5emUgdDsKClxkKwpcZGkrCgpleHBsYWluIGFuYWx5
emUgc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJvcCBpbmRleCBpZHhfMjsK
CmV4cGxhaW4gYW5hbHl6ZSBzZWxlY3QgKiBmcm9tIHQgd2hlcmUgYSA9IDEwMDA7Cg==
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproducer.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproducer.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0IChhIGludCwgYiBiaWdpbnQsIGMgdGV4dCkg
d2l0aCAoZmlsbGZhY3RvciA9IDUwKTsKCmluc2VydCBpbnRvIHQgc2VsZWN0IDEwMDAwMDAg
KiByYW5kb20oKSwgMTAwMDAwMCAqIHJhbmRvbSgpLCBtZDUoKDEwMDAwMDAgKiByYW5kb20o
KSk6OmludDo6dGV4dCkgZnJvbSBnZW5lcmF0ZV9zZXJpZXMoMSwxMDAwMDAwKSBzKGkpOwoK
c2VsZWN0IGNvdW50KCopIGZyb20gKHNlbGVjdCBkaXN0aW5jdCBiIGZyb20gdCB3aGVyZSBj
dGlkIDwgJygxMjgsMCknOjp0aWQpIGZvbzsKCmNyZWF0ZSBpbmRleCBibG9vbV9hX2lkeCBv
biB0IHVzaW5nIGJyaW4gKGEgaW50NF9ibG9vbV9vcHMobl9kaXN0aW5jdF9wZXJfcmFuZ2U9
NjAwMCkpOyAKCmFuYWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEg
PSAxMDAwOwoKY3JlYXRlIGluZGV4IGJyaW5faWR4IG9uIHQgdXNpbmcgYnJpbiAoYSk7CmFu
YWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJv
cCBpbmRleCBicmluX2lkeDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAx
MDAwOwo=
--------------D524CD37E77B36A2883A7617--
^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH 8/8] Consider selectivity in choose_bitmap_and
@ 2021-03-11 11:57 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 28+ messages in thread
From: Tomas Vondra @ 2021-03-11 11:57 UTC (permalink / raw)
---
src/backend/optimizer/path/indxpath.c | 38 ++++++++++++++++-----------
1 file changed, 23 insertions(+), 15 deletions(-)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index ff536e6b24..36b29c6c7b 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1437,6 +1437,7 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
foreach(l, paths)
{
Path *ipath = (Path *) lfirst(l);
+ bool duplicate = false;
pathinfo = classify_index_clause_usage(ipath, &clauselist);
@@ -1451,22 +1452,29 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
{
if (!pathinfoarray[i]->unclassifiable &&
bms_equal(pathinfo->clauseids, pathinfoarray[i]->clauseids))
- break;
- }
- if (i < npaths)
- {
- /* duplicate clauseids, keep the cheaper one */
- Cost ncost;
- Cost ocost;
- Selectivity nselec;
- Selectivity oselec;
-
- cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
- cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
- if (ncost < ocost)
- pathinfoarray[i] = pathinfo;
+ {
+ /* duplicate clauseids, keep the cheaper one
+ *
+ * XXX maybe this should just use path_usage_comparator?
+ */
+ Cost ncost;
+ Cost ocost;
+ Selectivity nselec;
+ Selectivity oselec;
+
+ cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
+ cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
+
+ if ((ncost < ocost) && (nselec < oselec))
+ {
+ pathinfoarray[i] = pathinfo;
+ duplicate = true;
+ break;
+ }
+ }
}
- else
+
+ if (!duplicate)
{
/* not duplicate clauseids, add to array */
pathinfoarray[npaths++] = pathinfo;
--
2.26.2
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproduce2.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproduce2.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0KGEgYmlnaW50KSB3aXRoIChmaWxsZmFjdG9y
ID0gMjUpOwoKaW5zZXJ0IGludG8gdCBzZWxlY3QgaSBmcm9tIGdlbmVyYXRlX3Nlcmllcygx
LDEwMDAwMDAwKSBzKGkpOwoKY3JlYXRlIGluZGV4IGlkeF8xIG9uIHQgdXNpbmcgYnJpbiAo
YSkgd2l0aCAocGFnZXNfcGVyX3JhbmdlID0gNCk7CgpjcmVhdGUgaW5kZXggaWR4XzIgb24g
dCB1c2luZyBicmluIChhKTsKCmFuYWx5emUgdDsKClxkKwpcZGkrCgpleHBsYWluIGFuYWx5
emUgc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJvcCBpbmRleCBpZHhfMjsK
CmV4cGxhaW4gYW5hbHl6ZSBzZWxlY3QgKiBmcm9tIHQgd2hlcmUgYSA9IDEwMDA7Cg==
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproducer.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproducer.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0IChhIGludCwgYiBiaWdpbnQsIGMgdGV4dCkg
d2l0aCAoZmlsbGZhY3RvciA9IDUwKTsKCmluc2VydCBpbnRvIHQgc2VsZWN0IDEwMDAwMDAg
KiByYW5kb20oKSwgMTAwMDAwMCAqIHJhbmRvbSgpLCBtZDUoKDEwMDAwMDAgKiByYW5kb20o
KSk6OmludDo6dGV4dCkgZnJvbSBnZW5lcmF0ZV9zZXJpZXMoMSwxMDAwMDAwKSBzKGkpOwoK
c2VsZWN0IGNvdW50KCopIGZyb20gKHNlbGVjdCBkaXN0aW5jdCBiIGZyb20gdCB3aGVyZSBj
dGlkIDwgJygxMjgsMCknOjp0aWQpIGZvbzsKCmNyZWF0ZSBpbmRleCBibG9vbV9hX2lkeCBv
biB0IHVzaW5nIGJyaW4gKGEgaW50NF9ibG9vbV9vcHMobl9kaXN0aW5jdF9wZXJfcmFuZ2U9
NjAwMCkpOyAKCmFuYWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEg
PSAxMDAwOwoKY3JlYXRlIGluZGV4IGJyaW5faWR4IG9uIHQgdXNpbmcgYnJpbiAoYSk7CmFu
YWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJv
cCBpbmRleCBicmluX2lkeDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAx
MDAwOwo=
--------------D524CD37E77B36A2883A7617--
^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH 8/8] Consider selectivity in choose_bitmap_and
@ 2021-03-11 11:57 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 28+ messages in thread
From: Tomas Vondra @ 2021-03-11 11:57 UTC (permalink / raw)
---
src/backend/optimizer/path/indxpath.c | 38 ++++++++++++++++-----------
1 file changed, 23 insertions(+), 15 deletions(-)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index ff536e6b24..36b29c6c7b 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1437,6 +1437,7 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
foreach(l, paths)
{
Path *ipath = (Path *) lfirst(l);
+ bool duplicate = false;
pathinfo = classify_index_clause_usage(ipath, &clauselist);
@@ -1451,22 +1452,29 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
{
if (!pathinfoarray[i]->unclassifiable &&
bms_equal(pathinfo->clauseids, pathinfoarray[i]->clauseids))
- break;
- }
- if (i < npaths)
- {
- /* duplicate clauseids, keep the cheaper one */
- Cost ncost;
- Cost ocost;
- Selectivity nselec;
- Selectivity oselec;
-
- cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
- cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
- if (ncost < ocost)
- pathinfoarray[i] = pathinfo;
+ {
+ /* duplicate clauseids, keep the cheaper one
+ *
+ * XXX maybe this should just use path_usage_comparator?
+ */
+ Cost ncost;
+ Cost ocost;
+ Selectivity nselec;
+ Selectivity oselec;
+
+ cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
+ cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
+
+ if ((ncost < ocost) && (nselec < oselec))
+ {
+ pathinfoarray[i] = pathinfo;
+ duplicate = true;
+ break;
+ }
+ }
}
- else
+
+ if (!duplicate)
{
/* not duplicate clauseids, add to array */
pathinfoarray[npaths++] = pathinfo;
--
2.26.2
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproduce2.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproduce2.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0KGEgYmlnaW50KSB3aXRoIChmaWxsZmFjdG9y
ID0gMjUpOwoKaW5zZXJ0IGludG8gdCBzZWxlY3QgaSBmcm9tIGdlbmVyYXRlX3Nlcmllcygx
LDEwMDAwMDAwKSBzKGkpOwoKY3JlYXRlIGluZGV4IGlkeF8xIG9uIHQgdXNpbmcgYnJpbiAo
YSkgd2l0aCAocGFnZXNfcGVyX3JhbmdlID0gNCk7CgpjcmVhdGUgaW5kZXggaWR4XzIgb24g
dCB1c2luZyBicmluIChhKTsKCmFuYWx5emUgdDsKClxkKwpcZGkrCgpleHBsYWluIGFuYWx5
emUgc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJvcCBpbmRleCBpZHhfMjsK
CmV4cGxhaW4gYW5hbHl6ZSBzZWxlY3QgKiBmcm9tIHQgd2hlcmUgYSA9IDEwMDA7Cg==
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproducer.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproducer.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0IChhIGludCwgYiBiaWdpbnQsIGMgdGV4dCkg
d2l0aCAoZmlsbGZhY3RvciA9IDUwKTsKCmluc2VydCBpbnRvIHQgc2VsZWN0IDEwMDAwMDAg
KiByYW5kb20oKSwgMTAwMDAwMCAqIHJhbmRvbSgpLCBtZDUoKDEwMDAwMDAgKiByYW5kb20o
KSk6OmludDo6dGV4dCkgZnJvbSBnZW5lcmF0ZV9zZXJpZXMoMSwxMDAwMDAwKSBzKGkpOwoK
c2VsZWN0IGNvdW50KCopIGZyb20gKHNlbGVjdCBkaXN0aW5jdCBiIGZyb20gdCB3aGVyZSBj
dGlkIDwgJygxMjgsMCknOjp0aWQpIGZvbzsKCmNyZWF0ZSBpbmRleCBibG9vbV9hX2lkeCBv
biB0IHVzaW5nIGJyaW4gKGEgaW50NF9ibG9vbV9vcHMobl9kaXN0aW5jdF9wZXJfcmFuZ2U9
NjAwMCkpOyAKCmFuYWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEg
PSAxMDAwOwoKY3JlYXRlIGluZGV4IGJyaW5faWR4IG9uIHQgdXNpbmcgYnJpbiAoYSk7CmFu
YWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJv
cCBpbmRleCBicmluX2lkeDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAx
MDAwOwo=
--------------D524CD37E77B36A2883A7617--
^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH 8/8] Consider selectivity in choose_bitmap_and
@ 2021-03-11 11:57 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 28+ messages in thread
From: Tomas Vondra @ 2021-03-11 11:57 UTC (permalink / raw)
---
src/backend/optimizer/path/indxpath.c | 38 ++++++++++++++++-----------
1 file changed, 23 insertions(+), 15 deletions(-)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index ff536e6b24..36b29c6c7b 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1437,6 +1437,7 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
foreach(l, paths)
{
Path *ipath = (Path *) lfirst(l);
+ bool duplicate = false;
pathinfo = classify_index_clause_usage(ipath, &clauselist);
@@ -1451,22 +1452,29 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
{
if (!pathinfoarray[i]->unclassifiable &&
bms_equal(pathinfo->clauseids, pathinfoarray[i]->clauseids))
- break;
- }
- if (i < npaths)
- {
- /* duplicate clauseids, keep the cheaper one */
- Cost ncost;
- Cost ocost;
- Selectivity nselec;
- Selectivity oselec;
-
- cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
- cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
- if (ncost < ocost)
- pathinfoarray[i] = pathinfo;
+ {
+ /* duplicate clauseids, keep the cheaper one
+ *
+ * XXX maybe this should just use path_usage_comparator?
+ */
+ Cost ncost;
+ Cost ocost;
+ Selectivity nselec;
+ Selectivity oselec;
+
+ cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
+ cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
+
+ if ((ncost < ocost) && (nselec < oselec))
+ {
+ pathinfoarray[i] = pathinfo;
+ duplicate = true;
+ break;
+ }
+ }
}
- else
+
+ if (!duplicate)
{
/* not duplicate clauseids, add to array */
pathinfoarray[npaths++] = pathinfo;
--
2.26.2
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproduce2.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproduce2.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0KGEgYmlnaW50KSB3aXRoIChmaWxsZmFjdG9y
ID0gMjUpOwoKaW5zZXJ0IGludG8gdCBzZWxlY3QgaSBmcm9tIGdlbmVyYXRlX3Nlcmllcygx
LDEwMDAwMDAwKSBzKGkpOwoKY3JlYXRlIGluZGV4IGlkeF8xIG9uIHQgdXNpbmcgYnJpbiAo
YSkgd2l0aCAocGFnZXNfcGVyX3JhbmdlID0gNCk7CgpjcmVhdGUgaW5kZXggaWR4XzIgb24g
dCB1c2luZyBicmluIChhKTsKCmFuYWx5emUgdDsKClxkKwpcZGkrCgpleHBsYWluIGFuYWx5
emUgc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJvcCBpbmRleCBpZHhfMjsK
CmV4cGxhaW4gYW5hbHl6ZSBzZWxlY3QgKiBmcm9tIHQgd2hlcmUgYSA9IDEwMDA7Cg==
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproducer.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproducer.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0IChhIGludCwgYiBiaWdpbnQsIGMgdGV4dCkg
d2l0aCAoZmlsbGZhY3RvciA9IDUwKTsKCmluc2VydCBpbnRvIHQgc2VsZWN0IDEwMDAwMDAg
KiByYW5kb20oKSwgMTAwMDAwMCAqIHJhbmRvbSgpLCBtZDUoKDEwMDAwMDAgKiByYW5kb20o
KSk6OmludDo6dGV4dCkgZnJvbSBnZW5lcmF0ZV9zZXJpZXMoMSwxMDAwMDAwKSBzKGkpOwoK
c2VsZWN0IGNvdW50KCopIGZyb20gKHNlbGVjdCBkaXN0aW5jdCBiIGZyb20gdCB3aGVyZSBj
dGlkIDwgJygxMjgsMCknOjp0aWQpIGZvbzsKCmNyZWF0ZSBpbmRleCBibG9vbV9hX2lkeCBv
biB0IHVzaW5nIGJyaW4gKGEgaW50NF9ibG9vbV9vcHMobl9kaXN0aW5jdF9wZXJfcmFuZ2U9
NjAwMCkpOyAKCmFuYWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEg
PSAxMDAwOwoKY3JlYXRlIGluZGV4IGJyaW5faWR4IG9uIHQgdXNpbmcgYnJpbiAoYSk7CmFu
YWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJv
cCBpbmRleCBicmluX2lkeDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAx
MDAwOwo=
--------------D524CD37E77B36A2883A7617--
^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH 8/8] Consider selectivity in choose_bitmap_and
@ 2021-03-11 11:57 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 28+ messages in thread
From: Tomas Vondra @ 2021-03-11 11:57 UTC (permalink / raw)
---
src/backend/optimizer/path/indxpath.c | 38 ++++++++++++++++-----------
1 file changed, 23 insertions(+), 15 deletions(-)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index ff536e6b24..36b29c6c7b 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1437,6 +1437,7 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
foreach(l, paths)
{
Path *ipath = (Path *) lfirst(l);
+ bool duplicate = false;
pathinfo = classify_index_clause_usage(ipath, &clauselist);
@@ -1451,22 +1452,29 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
{
if (!pathinfoarray[i]->unclassifiable &&
bms_equal(pathinfo->clauseids, pathinfoarray[i]->clauseids))
- break;
- }
- if (i < npaths)
- {
- /* duplicate clauseids, keep the cheaper one */
- Cost ncost;
- Cost ocost;
- Selectivity nselec;
- Selectivity oselec;
-
- cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
- cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
- if (ncost < ocost)
- pathinfoarray[i] = pathinfo;
+ {
+ /* duplicate clauseids, keep the cheaper one
+ *
+ * XXX maybe this should just use path_usage_comparator?
+ */
+ Cost ncost;
+ Cost ocost;
+ Selectivity nselec;
+ Selectivity oselec;
+
+ cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
+ cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
+
+ if ((ncost < ocost) && (nselec < oselec))
+ {
+ pathinfoarray[i] = pathinfo;
+ duplicate = true;
+ break;
+ }
+ }
}
- else
+
+ if (!duplicate)
{
/* not duplicate clauseids, add to array */
pathinfoarray[npaths++] = pathinfo;
--
2.26.2
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproduce2.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproduce2.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0KGEgYmlnaW50KSB3aXRoIChmaWxsZmFjdG9y
ID0gMjUpOwoKaW5zZXJ0IGludG8gdCBzZWxlY3QgaSBmcm9tIGdlbmVyYXRlX3Nlcmllcygx
LDEwMDAwMDAwKSBzKGkpOwoKY3JlYXRlIGluZGV4IGlkeF8xIG9uIHQgdXNpbmcgYnJpbiAo
YSkgd2l0aCAocGFnZXNfcGVyX3JhbmdlID0gNCk7CgpjcmVhdGUgaW5kZXggaWR4XzIgb24g
dCB1c2luZyBicmluIChhKTsKCmFuYWx5emUgdDsKClxkKwpcZGkrCgpleHBsYWluIGFuYWx5
emUgc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJvcCBpbmRleCBpZHhfMjsK
CmV4cGxhaW4gYW5hbHl6ZSBzZWxlY3QgKiBmcm9tIHQgd2hlcmUgYSA9IDEwMDA7Cg==
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproducer.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproducer.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0IChhIGludCwgYiBiaWdpbnQsIGMgdGV4dCkg
d2l0aCAoZmlsbGZhY3RvciA9IDUwKTsKCmluc2VydCBpbnRvIHQgc2VsZWN0IDEwMDAwMDAg
KiByYW5kb20oKSwgMTAwMDAwMCAqIHJhbmRvbSgpLCBtZDUoKDEwMDAwMDAgKiByYW5kb20o
KSk6OmludDo6dGV4dCkgZnJvbSBnZW5lcmF0ZV9zZXJpZXMoMSwxMDAwMDAwKSBzKGkpOwoK
c2VsZWN0IGNvdW50KCopIGZyb20gKHNlbGVjdCBkaXN0aW5jdCBiIGZyb20gdCB3aGVyZSBj
dGlkIDwgJygxMjgsMCknOjp0aWQpIGZvbzsKCmNyZWF0ZSBpbmRleCBibG9vbV9hX2lkeCBv
biB0IHVzaW5nIGJyaW4gKGEgaW50NF9ibG9vbV9vcHMobl9kaXN0aW5jdF9wZXJfcmFuZ2U9
NjAwMCkpOyAKCmFuYWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEg
PSAxMDAwOwoKY3JlYXRlIGluZGV4IGJyaW5faWR4IG9uIHQgdXNpbmcgYnJpbiAoYSk7CmFu
YWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJv
cCBpbmRleCBicmluX2lkeDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAx
MDAwOwo=
--------------D524CD37E77B36A2883A7617--
^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH 8/8] Consider selectivity in choose_bitmap_and
@ 2021-03-11 11:57 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 28+ messages in thread
From: Tomas Vondra @ 2021-03-11 11:57 UTC (permalink / raw)
---
src/backend/optimizer/path/indxpath.c | 38 ++++++++++++++++-----------
1 file changed, 23 insertions(+), 15 deletions(-)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index ff536e6b24..36b29c6c7b 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1437,6 +1437,7 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
foreach(l, paths)
{
Path *ipath = (Path *) lfirst(l);
+ bool duplicate = false;
pathinfo = classify_index_clause_usage(ipath, &clauselist);
@@ -1451,22 +1452,29 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
{
if (!pathinfoarray[i]->unclassifiable &&
bms_equal(pathinfo->clauseids, pathinfoarray[i]->clauseids))
- break;
- }
- if (i < npaths)
- {
- /* duplicate clauseids, keep the cheaper one */
- Cost ncost;
- Cost ocost;
- Selectivity nselec;
- Selectivity oselec;
-
- cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
- cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
- if (ncost < ocost)
- pathinfoarray[i] = pathinfo;
+ {
+ /* duplicate clauseids, keep the cheaper one
+ *
+ * XXX maybe this should just use path_usage_comparator?
+ */
+ Cost ncost;
+ Cost ocost;
+ Selectivity nselec;
+ Selectivity oselec;
+
+ cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
+ cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
+
+ if ((ncost < ocost) && (nselec < oselec))
+ {
+ pathinfoarray[i] = pathinfo;
+ duplicate = true;
+ break;
+ }
+ }
}
- else
+
+ if (!duplicate)
{
/* not duplicate clauseids, add to array */
pathinfoarray[npaths++] = pathinfo;
--
2.26.2
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproduce2.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproduce2.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0KGEgYmlnaW50KSB3aXRoIChmaWxsZmFjdG9y
ID0gMjUpOwoKaW5zZXJ0IGludG8gdCBzZWxlY3QgaSBmcm9tIGdlbmVyYXRlX3Nlcmllcygx
LDEwMDAwMDAwKSBzKGkpOwoKY3JlYXRlIGluZGV4IGlkeF8xIG9uIHQgdXNpbmcgYnJpbiAo
YSkgd2l0aCAocGFnZXNfcGVyX3JhbmdlID0gNCk7CgpjcmVhdGUgaW5kZXggaWR4XzIgb24g
dCB1c2luZyBicmluIChhKTsKCmFuYWx5emUgdDsKClxkKwpcZGkrCgpleHBsYWluIGFuYWx5
emUgc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJvcCBpbmRleCBpZHhfMjsK
CmV4cGxhaW4gYW5hbHl6ZSBzZWxlY3QgKiBmcm9tIHQgd2hlcmUgYSA9IDEwMDA7Cg==
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproducer.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproducer.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0IChhIGludCwgYiBiaWdpbnQsIGMgdGV4dCkg
d2l0aCAoZmlsbGZhY3RvciA9IDUwKTsKCmluc2VydCBpbnRvIHQgc2VsZWN0IDEwMDAwMDAg
KiByYW5kb20oKSwgMTAwMDAwMCAqIHJhbmRvbSgpLCBtZDUoKDEwMDAwMDAgKiByYW5kb20o
KSk6OmludDo6dGV4dCkgZnJvbSBnZW5lcmF0ZV9zZXJpZXMoMSwxMDAwMDAwKSBzKGkpOwoK
c2VsZWN0IGNvdW50KCopIGZyb20gKHNlbGVjdCBkaXN0aW5jdCBiIGZyb20gdCB3aGVyZSBj
dGlkIDwgJygxMjgsMCknOjp0aWQpIGZvbzsKCmNyZWF0ZSBpbmRleCBibG9vbV9hX2lkeCBv
biB0IHVzaW5nIGJyaW4gKGEgaW50NF9ibG9vbV9vcHMobl9kaXN0aW5jdF9wZXJfcmFuZ2U9
NjAwMCkpOyAKCmFuYWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEg
PSAxMDAwOwoKY3JlYXRlIGluZGV4IGJyaW5faWR4IG9uIHQgdXNpbmcgYnJpbiAoYSk7CmFu
YWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJv
cCBpbmRleCBicmluX2lkeDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAx
MDAwOwo=
--------------D524CD37E77B36A2883A7617--
^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH 8/8] Consider selectivity in choose_bitmap_and
@ 2021-03-11 11:57 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 28+ messages in thread
From: Tomas Vondra @ 2021-03-11 11:57 UTC (permalink / raw)
---
src/backend/optimizer/path/indxpath.c | 38 ++++++++++++++++-----------
1 file changed, 23 insertions(+), 15 deletions(-)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index ff536e6b24..36b29c6c7b 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1437,6 +1437,7 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
foreach(l, paths)
{
Path *ipath = (Path *) lfirst(l);
+ bool duplicate = false;
pathinfo = classify_index_clause_usage(ipath, &clauselist);
@@ -1451,22 +1452,29 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
{
if (!pathinfoarray[i]->unclassifiable &&
bms_equal(pathinfo->clauseids, pathinfoarray[i]->clauseids))
- break;
- }
- if (i < npaths)
- {
- /* duplicate clauseids, keep the cheaper one */
- Cost ncost;
- Cost ocost;
- Selectivity nselec;
- Selectivity oselec;
-
- cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
- cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
- if (ncost < ocost)
- pathinfoarray[i] = pathinfo;
+ {
+ /* duplicate clauseids, keep the cheaper one
+ *
+ * XXX maybe this should just use path_usage_comparator?
+ */
+ Cost ncost;
+ Cost ocost;
+ Selectivity nselec;
+ Selectivity oselec;
+
+ cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
+ cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
+
+ if ((ncost < ocost) && (nselec < oselec))
+ {
+ pathinfoarray[i] = pathinfo;
+ duplicate = true;
+ break;
+ }
+ }
}
- else
+
+ if (!duplicate)
{
/* not duplicate clauseids, add to array */
pathinfoarray[npaths++] = pathinfo;
--
2.26.2
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproduce2.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproduce2.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0KGEgYmlnaW50KSB3aXRoIChmaWxsZmFjdG9y
ID0gMjUpOwoKaW5zZXJ0IGludG8gdCBzZWxlY3QgaSBmcm9tIGdlbmVyYXRlX3Nlcmllcygx
LDEwMDAwMDAwKSBzKGkpOwoKY3JlYXRlIGluZGV4IGlkeF8xIG9uIHQgdXNpbmcgYnJpbiAo
YSkgd2l0aCAocGFnZXNfcGVyX3JhbmdlID0gNCk7CgpjcmVhdGUgaW5kZXggaWR4XzIgb24g
dCB1c2luZyBicmluIChhKTsKCmFuYWx5emUgdDsKClxkKwpcZGkrCgpleHBsYWluIGFuYWx5
emUgc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJvcCBpbmRleCBpZHhfMjsK
CmV4cGxhaW4gYW5hbHl6ZSBzZWxlY3QgKiBmcm9tIHQgd2hlcmUgYSA9IDEwMDA7Cg==
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproducer.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproducer.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0IChhIGludCwgYiBiaWdpbnQsIGMgdGV4dCkg
d2l0aCAoZmlsbGZhY3RvciA9IDUwKTsKCmluc2VydCBpbnRvIHQgc2VsZWN0IDEwMDAwMDAg
KiByYW5kb20oKSwgMTAwMDAwMCAqIHJhbmRvbSgpLCBtZDUoKDEwMDAwMDAgKiByYW5kb20o
KSk6OmludDo6dGV4dCkgZnJvbSBnZW5lcmF0ZV9zZXJpZXMoMSwxMDAwMDAwKSBzKGkpOwoK
c2VsZWN0IGNvdW50KCopIGZyb20gKHNlbGVjdCBkaXN0aW5jdCBiIGZyb20gdCB3aGVyZSBj
dGlkIDwgJygxMjgsMCknOjp0aWQpIGZvbzsKCmNyZWF0ZSBpbmRleCBibG9vbV9hX2lkeCBv
biB0IHVzaW5nIGJyaW4gKGEgaW50NF9ibG9vbV9vcHMobl9kaXN0aW5jdF9wZXJfcmFuZ2U9
NjAwMCkpOyAKCmFuYWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEg
PSAxMDAwOwoKY3JlYXRlIGluZGV4IGJyaW5faWR4IG9uIHQgdXNpbmcgYnJpbiAoYSk7CmFu
YWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJv
cCBpbmRleCBicmluX2lkeDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAx
MDAwOwo=
--------------D524CD37E77B36A2883A7617--
^ permalink raw reply [nested|flat] 28+ messages in thread
* [PATCH 8/8] Consider selectivity in choose_bitmap_and
@ 2021-03-11 11:57 Tomas Vondra <[email protected]>
0 siblings, 0 replies; 28+ messages in thread
From: Tomas Vondra @ 2021-03-11 11:57 UTC (permalink / raw)
---
src/backend/optimizer/path/indxpath.c | 38 ++++++++++++++++-----------
1 file changed, 23 insertions(+), 15 deletions(-)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index ff536e6b24..36b29c6c7b 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -1437,6 +1437,7 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
foreach(l, paths)
{
Path *ipath = (Path *) lfirst(l);
+ bool duplicate = false;
pathinfo = classify_index_clause_usage(ipath, &clauselist);
@@ -1451,22 +1452,29 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
{
if (!pathinfoarray[i]->unclassifiable &&
bms_equal(pathinfo->clauseids, pathinfoarray[i]->clauseids))
- break;
- }
- if (i < npaths)
- {
- /* duplicate clauseids, keep the cheaper one */
- Cost ncost;
- Cost ocost;
- Selectivity nselec;
- Selectivity oselec;
-
- cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
- cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
- if (ncost < ocost)
- pathinfoarray[i] = pathinfo;
+ {
+ /* duplicate clauseids, keep the cheaper one
+ *
+ * XXX maybe this should just use path_usage_comparator?
+ */
+ Cost ncost;
+ Cost ocost;
+ Selectivity nselec;
+ Selectivity oselec;
+
+ cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);
+ cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
+
+ if ((ncost < ocost) && (nselec < oselec))
+ {
+ pathinfoarray[i] = pathinfo;
+ duplicate = true;
+ break;
+ }
+ }
}
- else
+
+ if (!duplicate)
{
/* not duplicate clauseids, add to array */
pathinfoarray[npaths++] = pathinfo;
--
2.26.2
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproduce2.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproduce2.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0KGEgYmlnaW50KSB3aXRoIChmaWxsZmFjdG9y
ID0gMjUpOwoKaW5zZXJ0IGludG8gdCBzZWxlY3QgaSBmcm9tIGdlbmVyYXRlX3Nlcmllcygx
LDEwMDAwMDAwKSBzKGkpOwoKY3JlYXRlIGluZGV4IGlkeF8xIG9uIHQgdXNpbmcgYnJpbiAo
YSkgd2l0aCAocGFnZXNfcGVyX3JhbmdlID0gNCk7CgpjcmVhdGUgaW5kZXggaWR4XzIgb24g
dCB1c2luZyBicmluIChhKTsKCmFuYWx5emUgdDsKClxkKwpcZGkrCgpleHBsYWluIGFuYWx5
emUgc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJvcCBpbmRleCBpZHhfMjsK
CmV4cGxhaW4gYW5hbHl6ZSBzZWxlY3QgKiBmcm9tIHQgd2hlcmUgYSA9IDEwMDA7Cg==
--------------D524CD37E77B36A2883A7617
Content-Type: application/sql;
name="reproducer.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="reproducer.sql"
ZHJvcCB0YWJsZSBpZiBleGlzdHMgdDsKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJf
Z2F0aGVyID0gMDsKCmNyZWF0ZSB0YWJsZSB0IChhIGludCwgYiBiaWdpbnQsIGMgdGV4dCkg
d2l0aCAoZmlsbGZhY3RvciA9IDUwKTsKCmluc2VydCBpbnRvIHQgc2VsZWN0IDEwMDAwMDAg
KiByYW5kb20oKSwgMTAwMDAwMCAqIHJhbmRvbSgpLCBtZDUoKDEwMDAwMDAgKiByYW5kb20o
KSk6OmludDo6dGV4dCkgZnJvbSBnZW5lcmF0ZV9zZXJpZXMoMSwxMDAwMDAwKSBzKGkpOwoK
c2VsZWN0IGNvdW50KCopIGZyb20gKHNlbGVjdCBkaXN0aW5jdCBiIGZyb20gdCB3aGVyZSBj
dGlkIDwgJygxMjgsMCknOjp0aWQpIGZvbzsKCmNyZWF0ZSBpbmRleCBibG9vbV9hX2lkeCBv
biB0IHVzaW5nIGJyaW4gKGEgaW50NF9ibG9vbV9vcHMobl9kaXN0aW5jdF9wZXJfcmFuZ2U9
NjAwMCkpOyAKCmFuYWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEg
PSAxMDAwOwoKY3JlYXRlIGluZGV4IGJyaW5faWR4IG9uIHQgdXNpbmcgYnJpbiAoYSk7CmFu
YWx5emUgdDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAxMDAwOwoKZHJv
cCBpbmRleCBicmluX2lkeDsKCmV4cGxhaW4gc2VsZWN0ICogZnJvbSB0IHdoZXJlIGEgPSAx
MDAwOwo=
--------------D524CD37E77B36A2883A7617--
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: constants for tar header offsets
@ 2023-04-18 16:56 Dagfinn Ilmari Mannsåker <[email protected]>
2023-04-19 13:09 ` Re: constants for tar header offsets Robert Haas <[email protected]>
0 siblings, 1 reply; 28+ messages in thread
From: Dagfinn Ilmari Mannsåker @ 2023-04-18 16:56 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-hackers
Robert Haas <[email protected]> writes:
> On Tue, Apr 18, 2023 at 12:06 PM Tom Lane <[email protected]> wrote:
>> Hmm, you're right: I checked the POSIX.1-2018 spec as well, and
>> it agrees that the prefix field is 155 bytes long. Perhaps just
>> add another comment line indicating that 12 bytes remain unassigned?
>
> OK. Here's v2, with that change and a few others.
It still has magic numbers for the sizes of the fields, should those
also be named constants?
- ilmari
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: constants for tar header offsets
2023-04-18 16:56 Re: constants for tar header offsets Dagfinn Ilmari Mannsåker <[email protected]>
@ 2023-04-19 13:09 ` Robert Haas <[email protected]>
2023-08-01 15:07 ` Re: constants for tar header offsets Tristan Partin <[email protected]>
0 siblings, 1 reply; 28+ messages in thread
From: Robert Haas @ 2023-04-19 13:09 UTC (permalink / raw)
To: Dagfinn Ilmari Mannsåker <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-hackers
On Tue, Apr 18, 2023 at 12:56 PM Dagfinn Ilmari Mannsåker
<[email protected]> wrote:
> It still has magic numbers for the sizes of the fields, should those
> also be named constants?
I thought about that. It's arguable, but personally, I don't think
it's worth it. If the concern is greppability, having constants for
the offsets is good enough for that. If the concern is making it
error-free, I think we'd be well-advised to consider bigger redesigns
of the API. For example, we could have a function
read_number_from_tar_header(char *pointer_to_the_start_of_the_block,
enum which_field) and then that function could encapsulate the
knowledge of which tar numbers are 8 bytes and which are 12 bytes.
Writing read_number_from_tar_header(h, TAR_FIELD_CHECKSUM) seems
potentially less error-prone than
read_tar_number(&h[TAR_OFFSET_CHECKSUM], 8). On the other hand,
changing the latter to read_tar_number(&h[TAR_OFFSET_CHECKSUM],
TAR_LENGTH_CHECKSUM) seems longer but not necessarily cleaner. So I
felt it didn't make sense.
Just to be clear, I don't have a full vision for what a replacement
API ought to look like, and I'm not sure that figuring that out is
something that has to be done right this minute. I proposed this patch
not because it's perfect, but because it's simple. We can think of
doing more in the future if someone wants to devote the effort, and
that person might even be me, but right now it's not.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: constants for tar header offsets
2023-04-18 16:56 Re: constants for tar header offsets Dagfinn Ilmari Mannsåker <[email protected]>
2023-04-19 13:09 ` Re: constants for tar header offsets Robert Haas <[email protected]>
@ 2023-08-01 15:07 ` Tristan Partin <[email protected]>
2023-08-01 17:57 ` Re: constants for tar header offsets Robert Haas <[email protected]>
0 siblings, 1 reply; 28+ messages in thread
From: Tristan Partin @ 2023-08-01 15:07 UTC (permalink / raw)
To: Robert Haas <[email protected]>; Dagfinn Ilmari Mannsåker <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-hackers
On Wed Apr 19, 2023 at 8:09 AM CDT, Robert Haas wrote:
> On Tue, Apr 18, 2023 at 12:56 PM Dagfinn Ilmari Mannsåker
> <[email protected]> wrote:
> > It still has magic numbers for the sizes of the fields, should those
> > also be named constants?
>
> I thought about that. It's arguable, but personally, I don't think
> it's worth it. If the concern is greppability, having constants for
> the offsets is good enough for that. If the concern is making it
> error-free, I think we'd be well-advised to consider bigger redesigns
> of the API. For example, we could have a function
> read_number_from_tar_header(char *pointer_to_the_start_of_the_block,
> enum which_field) and then that function could encapsulate the
> knowledge of which tar numbers are 8 bytes and which are 12 bytes.
> Writing read_number_from_tar_header(h, TAR_FIELD_CHECKSUM) seems
> potentially less error-prone than
> read_tar_number(&h[TAR_OFFSET_CHECKSUM], 8). On the other hand,
> changing the latter to read_tar_number(&h[TAR_OFFSET_CHECKSUM],
> TAR_LENGTH_CHECKSUM) seems longer but not necessarily cleaner. So I
> felt it didn't make sense.
>
> Just to be clear, I don't have a full vision for what a replacement
> API ought to look like, and I'm not sure that figuring that out is
> something that has to be done right this minute. I proposed this patch
> not because it's perfect, but because it's simple. We can think of
> doing more in the future if someone wants to devote the effort, and
> that person might even be me, but right now it's not.
A new API design would be great, but for right now v2 is good enough and
should be committed. It is much easier to read the code with this patch
applied.
Marking as "Ready for Committer" since we all seem to agree that this is
better than what exists.
--
Tristan Partin
Neon (https://neon.tech)
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: constants for tar header offsets
2023-04-18 16:56 Re: constants for tar header offsets Dagfinn Ilmari Mannsåker <[email protected]>
2023-04-19 13:09 ` Re: constants for tar header offsets Robert Haas <[email protected]>
2023-08-01 15:07 ` Re: constants for tar header offsets Tristan Partin <[email protected]>
@ 2023-08-01 17:57 ` Robert Haas <[email protected]>
0 siblings, 0 replies; 28+ messages in thread
From: Robert Haas @ 2023-08-01 17:57 UTC (permalink / raw)
To: Tristan Partin <[email protected]>; +Cc: Dagfinn Ilmari Mannsåker <[email protected]>; Tom Lane <[email protected]>; pgsql-hackers
On Tue, Aug 1, 2023 at 11:07 AM Tristan Partin <[email protected]> wrote:
> A new API design would be great, but for right now v2 is good enough and
> should be committed. It is much easier to read the code with this patch
> applied.
>
> Marking as "Ready for Committer" since we all seem to agree that this is
> better than what exists.
Thanks, committed now.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 28+ messages in thread
end of thread, other threads:[~2023-08-01 17:57 UTC | newest]
Thread overview: 28+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-03-11 11:57 [PATCH 8/8] Consider selectivity in choose_bitmap_and Tomas Vondra <[email protected]>
2021-03-11 11:57 [PATCH 8/8] Consider selectivity in choose_bitmap_and Tomas Vondra <[email protected]>
2021-03-11 11:57 [PATCH 8/8] Consider selectivity in choose_bitmap_and Tomas Vondra <[email protected]>
2021-03-11 11:57 [PATCH 8/8] Consider selectivity in choose_bitmap_and Tomas Vondra <[email protected]>
2021-03-11 11:57 [PATCH 8/8] Consider selectivity in choose_bitmap_and Tomas Vondra <[email protected]>
2021-03-11 11:57 [PATCH 8/8] Consider selectivity in choose_bitmap_and Tomas Vondra <[email protected]>
2021-03-11 11:57 [PATCH 8/8] Consider selectivity in choose_bitmap_and Tomas Vondra <[email protected]>
2021-03-11 11:57 [PATCH 8/8] Consider selectivity in choose_bitmap_and Tomas Vondra <[email protected]>
2021-03-11 11:57 [PATCH 8/8] Consider selectivity in choose_bitmap_and Tomas Vondra <[email protected]>
2021-03-11 11:57 [PATCH 8/8] Consider selectivity in choose_bitmap_and Tomas Vondra <[email protected]>
2021-03-11 11:57 [PATCH 8/8] Consider selectivity in choose_bitmap_and Tomas Vondra <[email protected]>
2021-03-11 11:57 [PATCH 8/8] Consider selectivity in choose_bitmap_and Tomas Vondra <[email protected]>
2021-03-11 11:57 [PATCH 8/8] Consider selectivity in choose_bitmap_and Tomas Vondra <[email protected]>
2021-03-11 11:57 [PATCH 8/8] Consider selectivity in choose_bitmap_and Tomas Vondra <[email protected]>
2021-03-11 11:57 [PATCH 8/8] Consider selectivity in choose_bitmap_and Tomas Vondra <[email protected]>
2021-03-11 11:57 [PATCH 8/8] Consider selectivity in choose_bitmap_and Tomas Vondra <[email protected]>
2021-03-11 11:57 [PATCH 8/8] Consider selectivity in choose_bitmap_and Tomas Vondra <[email protected]>
2021-03-11 11:57 [PATCH 8/8] Consider selectivity in choose_bitmap_and Tomas Vondra <[email protected]>
2021-03-11 11:57 [PATCH 8/8] Consider selectivity in choose_bitmap_and Tomas Vondra <[email protected]>
2021-03-11 11:57 [PATCH 8/8] Consider selectivity in choose_bitmap_and Tomas Vondra <[email protected]>
2021-03-11 11:57 [PATCH 8/8] Consider selectivity in choose_bitmap_and Tomas Vondra <[email protected]>
2021-03-11 11:57 [PATCH 8/8] Consider selectivity in choose_bitmap_and Tomas Vondra <[email protected]>
2021-03-11 11:57 [PATCH 8/8] Consider selectivity in choose_bitmap_and Tomas Vondra <[email protected]>
2021-03-11 11:57 [PATCH 8/8] Consider selectivity in choose_bitmap_and Tomas Vondra <[email protected]>
2023-04-18 16:56 Re: constants for tar header offsets Dagfinn Ilmari Mannsåker <[email protected]>
2023-04-19 13:09 ` Re: constants for tar header offsets Robert Haas <[email protected]>
2023-08-01 15:07 ` Re: constants for tar header offsets Tristan Partin <[email protected]>
2023-08-01 17:57 ` Re: constants for tar header offsets Robert Haas <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox