Thread: Re: [HACKERS] "ExecInitIndexScan: both left and right..." meaning?
Well, the good news is that I understand this problem, and in fact fixed
it in current sources several months ago. The bad news is that the fix
is part of some rather extensive planner revisions, and I don't think
it's going to be safe/practical to back-patch it into REL6_5.
It's possible to duplicate the bug in 6.5.* with this test case:
create table t1 (f1 int, f2 int);
create index t1i on t1 (f1,f2);
create table t2 (f1 int, f2 int);
create index t2i on t2 (f1,f2);
create table t3 (f1 int, f2 int);
create index t3i on t3 (f1,f2);
select t1.f1 from t1,t2,t3 where
t1.f1=1 and t2.f1=1 and t3.f1=1 and
t1.f2=t2.f2 and t1.f2=t3.f2 and t2.f2=t3.f2;
ERROR: ExecInitIndexScan: both left and right op's are rel-vars
The generated query plan is
Nested Loop (cost=6.05 rows=1 width=16) -> Nested Loop (cost=4.05 rows=1 width=12) -> Index Scan using t3i on
t3 (cost=2.05 rows=1 width=4) -> Index Scan using t1i on t1 (cost=2.00 rows=1 width=8) -> Index Scan using t2i
ont2 (cost=2.00 rows=1 width=4)
and the source of the problem is that in the innermost indexscan on t1,
the planner is trying to use the WHERE clause "t1.f2=t2.f2" as an index
qualification. But it can't do that because in this query plan, t2
hasn't been joined to t1 yet. (It should have used "t1.f2=t3.f2"
instead; the value of t3.f2 is available since t3 is the outer side of
the nestloop join, meaning that in any one scan of t1, a fixed tuple
from t3 is being considered.)
The reason it makes this mistake is an ill-chosen data structure for
representing which WHERE clauses require which sets of outer relations
in order to be used as indexquals on the inside of a nestloop. In 6.5,
that info was attached to the first WHERE clause in the list of clauses
that might possibly be used with the index --- which in this example is
the t1.f1=1 clause. Trouble is, that clause can *also* be used in
joining t1 to t3, and there's only one of it, which means its
outer-join-relation field gets overwritten with the info for the join
considered last. So by the time we actually get around to generating
the plan, the clause list is marked as "OK to use in joining t1 to t3".
Oops.
I have fixed this in current sources by removing the field in question
from RestrictInfo nodes and storing the information in separate lists.
But it's a pretty major change and I don't want to try to back-patch it.
I would suggest, instead, that you work around the problem until 7.0
comes out. I think you could do this by removing your two-column
indexes in favor of single-column indexes, or even just switching the
order of the indexes (in the above test case, no bug is seen if the
indexes are declared on (f2,f1)). However switching the order would be
a bit fragile since it'd depend on which fields you compare to constants
and which ones you use as join keys in your queries. If that doesn't
work, a brute-force solution is to run your application with environment
variable PGOPTIONS="-fn" (forbid nestloop joins), which discourages the
planner from considering nestloop joins at all. The bug will not arise
if a merge or hash join plan is used.
regards, tom lane
Tom Lane wrote: > I would suggest, instead, that you work around the problem until 7.0 > comes out. I think you could do this by removing your two-column > indexes in favor of single-column indexes, or even just switching the > order of the indexes (in the above test case, no bug is seen if the > indexes are declared on (f2,f1)). However switching the order would be > a bit fragile since it'd depend on which fields you compare to constants > and which ones you use as join keys in your queries. If that doesn't > work, a brute-force solution is to run your application with environment > variable PGOPTIONS="-fn" (forbid nestloop joins), which discourages the > planner from considering nestloop joins at all. The bug will not arise > if a merge or hash join plan is used. First off, let me express appreciation for your investigation and explanation. My humble thanks. Re workarounds, I have removed all *unnecessary* multi-column indices. That still leaves me with 48 multi-column indices for primary keys and uniqueness. I think I must have those to avoid duplicate key problems, etc. Agreed, the index column order switching is fragile and will likely bite me later. So that leaves the "-fn" option. I will experiment with that. Are there any known consequences of forbidding nestloop joins? Performance hits? Functionality hits? Cheers, Ed Loehr
Ed Loehr wrote: > Are there any known consequences of forbidding nestloop joins? Performance > hits? Functionality hits? OK, I pulled out my old RDBMS text. Here are my current assumptions re join strategies: 1) The only Pgsql alternative join strategies to nested-loop joins are merge join and hash join. 2) Merge join only makes sense if the data is physically ordered by the join keys, and there is almost always a natural entropy away from physical sort order. Therefore, it generally makes sense to use only hash join. Are my assumptions correct? Reasonable conclusion? Can I configure psql to use only hash joins? Cheers, Ed Loehr
Ed Loehr <ELOEHR@austin.rr.com> writes:
> Re workarounds, I have removed all *unnecessary* multi-column indices.
> That still leaves me with 48 multi-column indices for primary keys and
> uniqueness. I think I must have those to avoid duplicate key problems,
> etc.
Yes, if you are using UNIQUE indexes to enforce uniqueness of primary
keys, then you don't have a lot of choice but to leave them in place.
So, if you still see the problem with only those multicolumn indexes
remaining, then PGOPTIONS="-fn" is your best recourse.
> Are there any known consequences of forbidding nestloop joins? Performance
> hits? Functionality hits?
You probably will see a performance hit, since the optimizer wouldn't be
picking the nestloop in the first place if it didn't think it was the
cheapest alternative. (Of course, whether its estimate is *right* is
another story...) Hopefully it won't be a large hit. The worst aspect
of using PGOPTIONS for this is that it'll affect all your queries not
just the ones where this trouble occurs; so on some simpler queries you
might see a noticeable slowdown. You will definitely want to remember
to take out the workaround once you are off 6.5.
A more significant potential problem is that the optimizer will use
nestloop anyway if it can't find a usable merge or hash join method.
I think that that won't be a problem for the datatypes you are using.
regards, tom lane
Ed Loehr <ELOEHR@austin.rr.com> writes:
> 1) The only Pgsql alternative join strategies to nested-loop joins are merge
> join and hash join.
Correct...
> 2) Merge join only makes sense if the data is physically ordered by the join
> keys, and there is almost always a natural entropy away from physical sort
> order.
> Therefore, it generally makes sense to use only hash join.
Not so. A merge join can be built atop either ordered-index-scans of
the inputs, or explicitly sorted input. Postgres' cost estimates are
done for both of these cases; if the optimizer thinks that merge join
is cheapest then it probably is.
> Can I configure psql to use only hash joins?
You could try PGOPTIONS="-fn -fm" to forbid both nestloop and merge
joins, but I wouldn't really recommend it. You'll be taking enough
of a performance hit from not using nestloop when it's cheapest;
disabling mergejoin as well doesn't seem like a good idea. Really
these options are intended as optimizer debugging aids, not as settings
that users should keep in place for long periods of time.
For the record, the other switches in this family are
-fh forbid hashjoin-fs forbid sequential scan-fi forbid indexed scan
Note that -fs/-fi are for individual scans and thus don't compete
with -fn/-fm/-fh for join methods. Also, -fs and -fn are not 100%
lockouts, since the optimizer will use those methods anyway if it
has no other choice (eg, -fs is ineffective if there's no index to
do an indexscan with).
regards, tom lane
I'm seeing an old showstopper bug in a new form in 6.5.2: ExecInitIndexScan: both left and right op's are rel-vars [I also sorely wish the error message identified the offending part of the WHERE clause.] I'm running with PGOPTIONS="-fn" (the previously assumed brute force prevention), and have partially verified there are no nested loops occurring (output & details below). Vacuum analyze no longer helps as it once did. This was presumed fixed in the coming 7.0, but the latest manifestation suggests the problem may not be fully understood. More below... For the sordid history, see http://www.deja.com/qs.xp?QRY=ExecInitIndexScan&OP=dnquery.xp&showsort=date or search Udm for ExecIndexInitScan. Here's context on where we last left it... Tom Lane wrote: > [...long explanation of bug and solution deleted...] > I have fixed this in current sources by removing the field in question > from RestrictInfo nodes and storing the information in separate lists. > But it's a pretty major change and I don't want to try to back-patch it. > > I would suggest, instead, that you work around the problem until 7.0 > comes out. I think you could do this by removing your two-column > indexes in favor of single-column indexes, or even just switching the > order of the indexes... However switching the order would be > a bit fragile since it'd depend on which fields you compare to constants > and which ones you use as join keys in your queries. If that doesn't > work, a brute-force solution is to run your application with environment > variable PGOPTIONS="-fn" (forbid nestloop joins), which discourages the > planner from considering nestloop joins at all. The bug will not arise > if a merge or hash join plan is used. It appears the -fn flag is not preventing the bug. One detail seems odd (and different from the nested-loop manifestation): when the offending "rather large" SELECT query is run via Apache/ mod_perl/DBI/DBD::Pg, the error occurs, but when I cut and paste the query from the logs into psql, it does not trigger the error (it also does not yield any results). I'm including the offending query and explain output below... Cheers, Ed Loehr SELECT sum( cet.default_budget_per_unit * cahrn.hr_count * cahrn.duration ) AS "amount" FROM contract_activity_hr_need cahrn, contract_expense_type cet, contract_activity_type_expense_type catet, contract_activity_typecat, activity pa WHERE cet.contract_id = 1 AND catet.contract_id = 1 AND cahrn.contract_id = 1 AND pa.contract_id = 1 AND cat.contract_id= 1 AND cet.expense_unit_id = 3 AND pa.activity_state_id <> 5 AND pa.activity_state_id <> 4 AND (pa.billable= 0 OR cahrn.billable = 0) AND pa.activity_type_id = cat.activity_type_id AND catet.expense_type_id = cet.expense_type_idAND catet.activity_type_id = cat.activity_type_id AND cahrn.contract_activity_type_id = cat.id; 20000109.02:13:48.783 [13865] NOTICE: QUERY PLAN: Aggregate (cost=28.61 rows=6608 width=52) -> Hash Join (cost=28.61 rows=6608 width=52) -> Hash Join (cost=14.74rows=1 width=44) -> Seq Scan on contract_activity_hr_need cahrn (cost=2.02 rows=3width=16) -> Hash (cost=11.58 rows=1 width=28) -> Merge Join (cost=11.58 rows=1 width=28) -> Seq Scan (cost=9.34 rows=1 width=20) -> Sort (cost=9.34 rows=1 width=20) -> Hash Join (cost=8.34 rows=1 width=20) -> Index Scan using contract_activi ty_type_exp_pkey on contract_activity_type_expense_ catet (cost=3.87 rows=38 wi dth=8) -> Hash (cost=2.18 rows=1 width=12 ) -> Index Scan using contract_ expense_type_pkey on contract_expense_type cet (cost=2.18 rows=1 width=12) -> Index Scan usingcontract_activity_type_pkey on co ntract_activity_type cat (cost=2.12 rows=3 width=8) -> Hash (cost=13.84 rows=0 width=8) -> Index Scanusing activity_cid on activity pa (cost=13.84 rows =0 width=8)