Tom Lane wrote:
> Does the EXPLAIN output showing the query plan change from when it's
> working to when it's not? What would really be helpful is to see the
> EXPLAIN VERBOSE output in both states (preferably, the pretty-printed
> version that gets put in the postmaster log file, not the compressed
> version that gets sent to the client).
Yes, the query plan changes between working state and non-working state.
Vaccum triggers the change. Other things may also, I'm not sure yet. Here
are the failing and successful query plans, respectively...
QUERY PLAN: (failed due to ExecInitIndexScan left/right rel op error)
Aggregate (cost=10.05 rows=1 width=48) -> Nested Loop (cost=10.05 rows=1 width=48) -> Nested Loop (cost=8.05
rows=1width=36) -> Nested Loop (cost=6.05 rows=1 width=24) -> Nested Loop (cost=4.05
rows=1width=16) -> Index Scan using activity_cid on activity pa (cost=2.05 rows=1 width=8)
-> Index Scan using contract_activity_type_pkey on contract_activity_type cat (cost=2.00 rows=2
width=8) -> Index Scan using contract_activity_type_exp_pkey on contract_activity_type_expense_ catet
(cost=2.00 rows=2 width=8) -> Index Scan using contract_expense_type_pkey on contract_expense_type cet
(cost=2.00rows=1 width=12) -> Index Scan using contract_activity_hr_need_pkey on contract_activity_hr_need cahrn
(cost=2.00 rows=2 width=12)
VACUUM
QUERY PLAN: (successful query after vacuuming)
Aggregate (cost=9.58 rows=1 width=48) -> Nested Loop (cost=9.58 rows=1 width=48) -> Nested Loop (cost=7.58
rows=1width=36) -> Nested Loop (cost=5.53 rows=1 width=28) -> Nested Loop (cost=3.53
rows=1width=16) -> Seq Scan on contract_activity_type cat (cost=1.53 rows=1 width=8)
-> Index Scan using contract_activity_type_exp_pkey on contract_activity_type_expense_ catet (cost=2.00
rows=2width=8) -> Index Scan using contract_expense_type_pkey on contract_expense_type cet
(cost=2.00rows=1 width=12) -> Index Scan using activity_cid on activity pa (cost=2.05 rows=1 width=8)
-> Index Scan using contract_activity_hr_need_pkey on contract_activity_hr_need cahrn (cost=2.00 rows=2 width=12)
Other ideas?
Cheers,
Ed Loehr