On 7/8/22 03:07, Tom Lane wrote:
Andrey Lepikhov <a.lepik...@postgrespro.ru> writes:On 12/8/21 04:26, Tomas Vondra wrote:I wonder if we should teach clauselist_selectivity about UNIQUE indexes, and improve the cardinality estimates directly, not just costing for index scans.I tried to implement this in different ways. But it causes additional overhead and code complexity - analyzing a list of indexes and match clauses of each index with input clauses in each selectivity estimation. I don't like that way and propose a new patch in attachment.I looked at this briefly. I do not think that messing with btcostestimate/genericcostestimate is the right response at all. The problem can be demonstrated with no index whatever, as in the attached shortened version of the original example. I get
I partly agree with you. Yes, I see the problem too. But also we have a problem that I described above: optimizer don't choose a path with minimal selectivity from a set selectivities which shows cardinality less than 1 (see badestimate2.sql).
New patch (see in attachment), fixes this problem. -- Regards Andrey Lepikhov Postgres Professional
From 463c085852be921199b7a6d0987378d55145e41c Mon Sep 17 00:00:00 2001
From: Andrey Lepikhov <a.lepik...@postgrespro.ru>
Date: Mon, 11 Jul 2022 12:25:43 +0500
Subject: [PATCH] Use Index path with the best selectivity estimation
---
src/backend/optimizer/util/pathnode.c | 14 ++++++++++++++
1 file changed, 14 insertions(+)
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 483c4f4137..a8d7f22343 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -207,6 +207,20 @@ compare_path_costs_fuzzily(Path *path1, Path *path2, double fuzz_factor)
/* ... but path2 fuzzily worse on startup, so path1 wins */
return COSTS_BETTER1;
}
+ if (IsA(path1, IndexPath) && IsA(path2, IndexPath))
+ {
+ /*
+ * Couldn't differ value of the paths. Last chance - if these paths
+ * are index paths - use the path with the lower selectivity value.
+ */
+ if (((IndexPath *) path1)->indexselectivity <
+ ((IndexPath *) path2)->indexselectivity)
+ return COSTS_BETTER1;
+
+ if (((IndexPath *) path1)->indexselectivity >
+ ((IndexPath *) path2)->indexselectivity)
+ return COSTS_BETTER2;
+ }
/* fuzzily the same on both costs */
return COSTS_EQUAL;
--
2.34.1
badestimate2.sql
Description: application/sql
