Re: Predicate migration on complex self joins

Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Writing a join for a single-table query?  Why, in heavens name?
> (Or have you mercifully blotted the details from your memory?)
Actually, I had only the vaguest recollection of why, but I found an
email where I was explaining the problem to Sybase.  Basically, it
boiled down to a corner case involving the intersection of named
caches and index optimizations similar to what Heikki's currently
developing.  If we did a search such as:
SELECT searchName FROM Party WHERE searchName LIKE 'PET%,RANDY%'
where searchName was the first column of an index bound to a named
cache, it would scan the range of the index where searchName >= 'PET'
and searchName < 'PEU', determine which rows actually matched the
whole pattern, and access the heap pages only for those rows which
matched the pattern.  (In this case, 298 rows.)  As long as only
columns from the index were returned, there were only 298 access to
the heap.  Now, if you added a column which was not in the index, it
went to the heap before checking the full pattern, so it went to the
heap 87,632 times for the above criteria, and returned the same 298
rows.  Since the primary key columns were in all indexes (to allow use
of READ UNCOMMITTED :-/ ), we could select those columns without
driving it to the heap, so we used the first table reference just
for selecting the rows, and joined back to the same table on primary
key to get the values to return.
We could not convince Sybase that they should fix that issue.
-Kevin