index filter
A user asked what the difference is between "post index filter" and "lower/upper index filter" in an Informix query explain plan, and which is more expensive. Answers explained that lower and upper index filters define the start and stop points of an index scan (e.g. for >=, <, or BETWEEN conditions) and cost nothing extra — the upper filter just short-circuits the scan early — whereas a post index filter is applied after the index lookup and must read the actual data pages, making it the costlier option. Question answered.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
What is the difference between PostIndex Filter and Lower Index Filter in Explain plan.
KHURRAM SHAHZAD wrote: > What is the difference between PostIndex Filter and Lower Index Filter > in Explain plan. The first is applied after you have searched the index, the second is applied when you search the index. -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
which one is costly
KHURRAM SHAHZAD wrote: > which one is costly Post index. -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
is upper index filter's cost less than the post index filter?
KHURRAM SHAHZAD wrote: > is upper index filter's cost less than the post index filter? Yes. The index search is relatively fast and low cost, the post index filter requires inspecting the actual row data, so much more work. -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
Lower index filter is just the starting point for an indexed scan, usually to satisfy a > or >= condition. The postindex filter is a filter applied to the data rows selected by the indexes search. Since it has to read data pages in order to further filter the rows it is more costly than an indexed search that doesn't have to read data pages. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Aug 12, 2010 at 11:16 AM, KHURRAM SHAHZAD <kshahzad02@i2cinc.com>wrote: > which one is costly > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --002215b032561073a9048da2ce93
When you have an upper index filter and a lower index filter it means that the query specifies something like: where colname >= value1 and colname < value2 OR where colname between value1 and value2 OR the equivalent join condition to a column in another table. The engine will find the LOWER FILTER value, then scan across the leaves of the index picking up all of the rowids until it finds a key that meets the UPPER FILTER value where upon it stops scanning. There is no additional cost to an UPPER INDEX FILTER, it just short circuits the index scan before the end of the index is reached. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Aug 12, 2010 at 11:33 AM, KHURRAM SHAHZAD <kshahzad02@i2cinc.com>wrote: > is upper index filter's cost less than the post index filter? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016367fb02da60006048da2da6e