Re: [HACKERS] "ExecInitIndexScan: both left and right..." meaning?
От | Ed Loehr |
---|---|
Тема | Re: [HACKERS] "ExecInitIndexScan: both left and right..." meaning? |
Дата | |
Msg-id | 38784811.3E555463@austin.rr.com обсуждение исходный текст |
Ответ на | Re: [HACKERS] "ExecInitIndexScan: both left and right..." meaning? (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-hackers |
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)
В списке pgsql-hackers по дате отправления:
Предыдущее
От: The Hermit HackerДата:
Сообщение: Re: [HACKERS] New scheme for managing regress test result files