Re: [SQL] Please, HELP! Why is the query plan so wrong???

Jie Liang wrote:

please copy and paste the whole msg and your query!
Note:what I mean ' join key' is the fields that link two tables.

The message (query plan) is exactly the same (you can see it in the
bottom of this message).

I don't think fb.b=0 is a join key!

Of course not. But it IS using the index on fb. It is the fbr, that is
the problem (and the join key is explicitly specified in the join... on
(..) clause).

Actually, it looks like the problem is caused by the predicate on the index:
if instead of

create index fbr_idx on fbr(a,c) where d is null;

I do just:

create index fbr_idx on fbr(a,c,d);

Then this index is used by the query...

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.
Ater debugging it a little, I found out that, if I list the tables in
the query in the opposite order:

explain select * from fbr, fb where fb.b=0 and fb.a=fbr.a and
fb.c=fbr.c and fbr.d is null;

Then it works (both indexes are used!).

Here is what is causing it:
create_index_paths() calls pred_test () to check whether it is OK to
use a partial index or not.
pred_test () evaluates the index's predicate list against the
restriction clauses of the query.
In my case, the predicate list "d is NULL", so the goal is to find the
equivalent clause in the query.
pred_test () does that by iterating through the query's clauses and
comparing them to the predicate with the equal() function.
equal () calls _equalNullTest(), which in turn calls _equalVar(), that
looks at the varno parameter in the NullTest's argument. Now the value
of varno in the predicate is (of course) 1, (I believe, it always is,
because we don't have multitable indexes), however, the varno in the
clause is 2 (it is the index of the table in the join list), if the fbr
is listed second - and 1, if it is first - so, in the former case it
does not work, and in the latter it does.

Knowing all this doesn't help much unfortunately, because, if you needed
to join 2 (or more) tables that have indexes with predicates, then
whatever order you put them in, would not help (the pred_test() will
only succeed for the first table in the join list) :-(

Perhaps, somebody, familiar with this code could come up with a patch
for this problem?

This would be really great!

Dima

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

*** 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)
{