[SQL] Indexes with LIKE
I've noticed a difference in behaviour between a server running version 6.x and one running 7.2.1 in use of indexes with LIKE. With an index on foo, WHERE foo LIKE 'bar%' uses the index (as I would expect it to) on the 6.x box, but uses a sequential scan (really slow) on the 7.2.1 box. It's possible that I've set (or failed to set) a run-time parameter that controls the behaviour, but I don't know what that might be. ANALYZEing the table doesn't modify the behaviour. I can't find any mention of this in the documentation -- any pointers would be appreciated. If the above doesn't ring any bells, I'll put together an example. Many thanks Julian Scarfe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] pg_restore cannot restore index
Jie Liang wrote:
> On this point, I'd like to ask:
> 1. where I can download this new version?
> 2. does
> pg_restore --index=aa --dbname=test /bjm/x
> works also???
OK, the attached patch should allow -I to work in 7.2.X. This will all
be fixed in 7.3.
> Because
> pg_restore --table=mytable --dbname=mydb mydumpfile
> doesn't work!
Is this a different problem? --table doesn't work either?
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup.| Drexel Hill, Pennsylvania 19026
*** ./pg_restore.c.orig Sat Jul 13 10:45:43 2002
--- ./pg_restore.c Sat Jul 13 10:45:55 2002
***
*** 163,171
}
#ifdef HAVE_GETOPT_LONG
! while ((c = getopt_long(argc, argv,
"acCd:f:F:h:i:lL:NoOp:P:rRsS:t:T:uU:vWxX:", cmdopts, NULL)) != -1)
#else
! while ((c = getopt(argc, argv, "acCd:f:F:h:i:lL:NoOp:P:rRsS:t:T:uU:vWxX:")) !=
-1)
#endif
{
switch (c)
--- 163,171
}
#ifdef HAVE_GETOPT_LONG
! while ((c = getopt_long(argc, argv,
"acCd:f:F:h:iI:lL:NoOp:P:rRsS:t:T:uU:vWxX:", cmdopts, NULL)) != -1)
#else
! while ((c = getopt(argc, argv, "acCd:f:F:h:iI:lL:NoOp:P:rRsS:t:T:uU:vWxX:"))
!= -1)
#endif
{
switch (c)
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Indexes with LIKE
On Sat, 13 Jul 2002, Julian Scarfe wrote: > I've noticed a difference in behaviour between a server running version 6.x > and one running 7.2.1 in use of indexes with LIKE. > > With an index on foo, > > WHERE foo LIKE 'bar%' > > uses the index (as I would expect it to) on the 6.x box, but uses a > sequential scan (really slow) on the 7.2.1 box. > It's possible that I've set (or failed to set) a run-time parameter that > controls the behaviour, but I don't know what that might be. ANALYZEing the > table doesn't modify the behaviour. > > I can't find any mention of this in the documentation -- any pointers would > be appreciated. You need to have made the database in C locale in order to get index scans from LIKE. I think that's mentioned in the Localization section of the admin guide, but I could be remembering that wrong. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] It is a bug in pred_test()! (Was: [SQL] Please, HELP! Why is the query plan so wrong???)
Dmitry Tkach <[EMAIL PROTECTED]> 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)
{
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
