AA van Raalte wrote:
>
> >>
> >> Summary: Indexes are broken
> >
> >Could you post me your data to give me way to reproduce bug locally ?
> >
> >Vadim
>
> OK. There are three scripts attatched, s1 generates the data, s2 creates the
> indexes, s3 performs a query on the data.
> If you perform the following query:
>
> cat s1 s3 | psql db
>
> the last query returns a count of 8.
> If you use indexes by performing the following, identical query:
>
> cat s1 s2 s3 | psql db
>
> the last query returns a count of 4!.
Well, fortunately, btree are not broken and two old bugs fixed. I suppose
that your server compiled without CASSERT. Having CASSERT I got core from
the next s3' query when tested using indices:
update temp_bankbaln
set prev_baln = b.baln
from temp_bankbaln b
where temp_bankbaln.prev_date = b.date and
temp_bankbaln.accno = b.accno;
EXPLAIN:
Merge Join (cost=53.85 size=24556 width=62)
-> Index Scan on b (cost=19.40 size=228 width=24)
-> Index Scan on temp_bankbaln (cost=19.40 size=228 width=38)
Without index baln_i4 on temp_bankbaln(accno):
Merge Join (cost=71.18 size=24556 width=60)
-> Index Scan on b (cost=18.40 size=228 width=24)
-> Seq Scan (cost=18.40 size=0 width=0)
-> Sort (cost=10.52 size=0 width=0)
-> Seq Scan on temp_bankbaln (cost=10.52 size=228 width=36)
- no probs.
First bug was in execScan.c when "bad" tuple table slot was returned in
the end of index' scan and the second was in nodeIndexscan.c where
"tooo" general and dummy IndexScanMarkPosition()/ExecIndexRestrPos()
were used instead of index_markpos()/index_restrpos().
Note, that ONLY MergeJoin was affected by these two bugs because
this join method is only one using ExecMarkPos() and ExecRestrPos()
(and for inner child plan onle).
BTW, SELECT with second plan above is 13 times faster than with
the first one! Sorting by using indices is not always fastest way...
Thanks Alvin for given simple way to reproduce bug!
BTW, elog(FATAL) from vacuum and btree were reported by 2-3
another ppl - shouldn't we ask users to re-compile server with
CASSERT in all "FATAL" cases ?
CASSERT assisted me very much to find real sources of this problem
and recently fixed vacuum bug.
Vadim
P.S. Unfortunately, I still didn't add permissions check to subselect
code and didn't implement ReScan of MergeJoin node - hope to do this
tomorrow... Bye.
