From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dmitry Tkach <dmitry@openratings.com>
Cc: Jie Liang <jie@stbernard.com>
Cc: pgsql-general@postgresql.org
Cc: pgsql-sql@postgresql.org
Cc: pgsql-bugs@postgresql.org
Subject: Re: It is a bug in pred_test()! (Was: [SQL] Please, HELP! Why is the query plan so wrong???)
Date: Sat, 13 Jul 2002 15:27:27 -0400
Message-ID: <10940.1026588447@sss.pgh.pa.us> (raw)
In-Reply-To: <3D2F010E.4050707@openratings.com>
References: <7C760DAA511DC74B99E7D22189F786F101BF211D@MAIL01.stbernard.com>
<3D2F010E.4050707@openratings.com>
Dmitry Tkach <dmitry@openratings.com> writes:
> It now looks like a bug in the query planner to me - it seems that it
> just doesn't consider indices with predicates for join plans...
> I was looking at the source code, and it looks like pred_test() is
> responsible for that.
Yup. I've applied the attached patch, which seems to solve the problem
in CVS tip. I haven't tested it in the REL7_2 branch, but I believe it
will work if you want to patch locally.
regards, tom lane
inline patch 1: src/backend/optimizer/path/indxpath.c (+18/-3 lines) download inline-1.patch*** src/backend/optimizer/path/indxpath.c.orig Fri Jun 21 14:17:33 2002
--- src/backend/optimizer/path/indxpath.c Sat Jul 13 14:57:26 2002
***************
*** 35,40 ****
--- 35,41 ----
#include "parser/parse_coerce.h"
#include "parser/parse_expr.h"
#include "parser/parse_oper.h"
+ #include "rewrite/rewriteManip.h"
#include "utils/builtins.h"
#include "utils/fmgroids.h"
#include "utils/lsyscache.h"
***************
*** 79,85 ****
int indexkey, Oid opclass,
Expr *clause, bool join);
static bool pred_test(List *predicate_list, List *restrictinfo_list,
! List *joininfo_list);
static bool pred_test_restrict_list(Expr *predicate, List *restrictinfo_list);
static bool pred_test_recurse_clause(Expr *predicate, Node *clause);
static bool pred_test_recurse_pred(Expr *predicate, Node *clause);
--- 80,86 ----
int indexkey, Oid opclass,
Expr *clause, bool join);
static bool pred_test(List *predicate_list, List *restrictinfo_list,
! List *joininfo_list, int relvarno);
static bool pred_test_restrict_list(Expr *predicate, List *restrictinfo_list);
static bool pred_test_recurse_clause(Expr *predicate, Node *clause);
static bool pred_test_recurse_pred(Expr *predicate, Node *clause);
***************
*** 153,159 ****
* predicate test.
*/
if (index->indpred != NIL)
! if (!pred_test(index->indpred, restrictinfo_list, joininfo_list))
continue;
/*
--- 154,161 ----
* predicate test.
*/
if (index->indpred != NIL)
! if (!pred_test(index->indpred, restrictinfo_list, joininfo_list,
! lfirsti(rel->relids)))
continue;
/*
***************
*** 957,963 ****
* to CNF format). --Nels, Jan '93
*/
static bool
! pred_test(List *predicate_list, List *restrictinfo_list, List *joininfo_list)
{
List *pred;
--- 959,966 ----
* to CNF format). --Nels, Jan '93
*/
static bool
! pred_test(List *predicate_list, List *restrictinfo_list, List *joininfo_list,
! int relvarno)
{
List *pred;
***************
*** 979,984 ****
--- 982,999 ----
if (restrictinfo_list == NIL)
return false; /* no restriction clauses: the test must
* fail */
+ + /*+ * The predicate as stored in the index definition will use varno 1+ * for its Vars referencing the indexed relation. If the indexed+ * relation isn't varno 1 in the query, we must adjust the predicate+ * to make the Vars match, else equal() won't work.+ */+ if (relvarno != 1)+ {+ predicate_list = copyObject(predicate_list);+ ChangeVarNodes((Node *) predicate_list, 1, relvarno, 0);+ }
foreach(pred, predicate_list)
{
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, dmitry@openratings.com, jie@stbernard.com, pgsql-general@postgresql.org, pgsql-bugs@postgresql.org
Subject: Re: It is a bug in pred_test()! (Was: [SQL] Please, HELP! Why is the query plan so wrong???)
In-Reply-To: <10940.1026588447@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox