David Beavon (Software) asked a question.

Edited April 29, 2026 at 11:11 PM
What sort of query plans don't require the use of the base table?

I have a question about query plans in the SQL engine. I had asked a Progress consultant long ago (10 years?) and got a response that was useful to me at the time, but it was never written down or posted to the Internet. So I need to revisit again...

 

What are the requirements to execute a query that is ONLY resolved from index data, (while avoiding the base table)? In ABL I think it is virtually impossible to write such queries. But in the SQL engine I know there are some scenarios where it happens (albeit the scenarios aren't as common as when using other modern query engines ).

 

Would such a plan be possible if ALL my columns are present in the index? Is there any chance I can get also get the ROWID as a bonus, without reaching back to the base table for that?

 

I vaguely remember the answer I got a decade ago. At that time I was told that any query plans using SQL engine almost always hit the base table. This behavior was only avoided for columns that are numeric and have fixed widths. I think character fields were off-limits for index-only query plans. I think the reasoning was because Progress had some super-special storage mechanisms for character data. (It is also possible that those were the days of "abbreviated indexes", which may not have been compatible with the SQL query engine).

 

I'm not 100% certain about this, but I think another prerequisite for these sorts of query plans is that we can't use any isolation level other than READ UNCOMMITTED.

 

I would appreciate some guidance in how we might write index-only queries for the SQL engine. I will end up reviewing my query plans (sql_qplan) to verify the behavior no matter what. But it would be helpful to discuss here, so that I will have a point-of-reference. Then I will be able to refer to this discussion when I'm revisiting this topic again after another ten years .

 

  • 6 answers
  • 122 views
Peter Judge and Mike Fechner like this.

  • David Beavon (Software)

    I opened a tech support case, and got a reply.

     

    "

    Our development team has confirmed that it is not possible to fetch the data directly from the index. 

    "

     

    ... so apparently the information that I received from a Progress consultant ten years ago was not correct.

    Hope this helps others.

    Selected as Best

  • Dmitri Levin (Customer)

    > I think character fields were off-limits for index-only query plans. I think the reasoning was because Progress had some super-special storage mechanisms for character data.

     

    Converting a character field value to a sorting weight is guaranteed to work only in one direction. The reverse conversion may not work even for case-sensitive sorting.

     

    Index_key = Sorting_Weight(Character_Field).

    • David Beavon (Software)

      Are you confirming that character fields prevent me from resolving queries from indexes? (I always google to chase down more info but it isn't helping much. And the LLMs always confuse progress and postgres, so these searches generally aren't productive - I get too much noise and can't believe any answers composed by the AI.)

       

       

      The "one direction" implies navigating the index B-Tree or something?

       

      What if I had fields: CharField1 and CharField2 as the top two index colums, and I ran a query that needed no other data:

       

      SELECT DISTINCT CharField1, CharField2 FROM Table001

       

      Would this be allowed (at least in theory) based on your explanation, since we are navigating the whole index and can do it in the direction of our choosing?

       

      In practice will the OE SQL engine be smart enough to avoid the base table?

       

      What about ROWID? That is not a character; so will the SQL engine be smart enough to pull that out of index-based queries without reading pages from the base table?

       

      I appreciate your help. These questions are not totally abstract. Our Progress OE servers run at 100% for some parts of the day - across all cores. Moving ABL/interpreted code (from shared memory) over to the native SQL broker is likely to reduce the overall CPU by a 10% if we focus on the worst performing read operations. While doing that work, it would be nice to understand when we can take things forward yet another step and avoid the base table as well. Realistically speaking that might not happen for more than 2% of our queries. But sometimes those queries are big ones (like performing a survey of a month of data, in order to display a scroll bar for a table of sorted rows.) If you can regularly avoid going back to the base table to get 100k of rows, then it is often worthwhile to spend a little more time on the queries.

       

       

  • David Beavon (Software)

    As far as ROWID goes, I am pretty convinced that this engine isn't able to use an index for that information.

    ... which is very unfortunate, since it is probably far faster and more useful to get that off an index than off the base table

     

     

    Here is a query that says pretty clearly that the optimizer uses the base table instead of scanning an index:

     

    SELECT COUNT(DISTINCT Test) FROM (SELECT CAST(ROWID AS VARCHAR(20)) AS Test FROM pub."mytable" ) X

     

     

    For a two-million row table this takes way too long (30 seconds).

     

    imageSome of that may be to the temp table and the sorting, but the vast majority is probably related to the table scan.

     

     

     

    ...

    SIDE NOTE:

    As a side note, there is some sort of weird bug when getting rowid from a 2MM row table like so:

    SELECT COUNT(DISTINCT my_item.ROWID) FROM pub."my_item"

     

    Insufficient Memory. Memory allocation failed at 752 in Z:/vobs_sql/sql/src/rds/psr_bld1.cxx. Contact Progress Technical Support

     

    If I change the query to use a subselect the query completes fine (albeit ~30 seconds for just 2MM rows):

    SELECT COUNT(DISTINCT Test) FROM (SELECT my_item.ROWID AS Test FROM pub."my_item" ) X

     

     

     

     

     

     

  • David Beavon (Software)

    As far as character fields go, it looks like those queries aren't resolved exclusively by an index, although the index participates.

     

    Here is the evidence. There are two character columns in my table. The first is desc4 which is only on the base table, and the second is entity which is in the top position of an index. The cardinalities of the distinct values is negligible (under 1000). The number of total number of rows in the table is 2 million.

     

    When querying the base table, it looks like this.

     

     

    SELECT COUNT(DISTINCT Test) FROM (SELECT DISTINCT pro_item."desc4" AS Test FROM pub."pro_item" ) X

     

     

    image 

    When querying the index it looks like this:

     

    SELECT COUNT(DISTINCT Test) FROM (SELECT DISTINCT pro_item."entity" AS Test FROM pub."pro_item" ) X

     

    image 

     

     

    Here is the really interesting part. The query on "desc4", using the table scan is always FASTER than the second query that relies on the index ("entity"). The table scan is consistently about 3.0 seconds, and the index scan is consistently about 3.8 seconds (!)

     

    I'm guessing the index scan is having to first read index pages for 0.8 seconds, and is also reading the base table for 3 seconds. That is why it is slower when the query can find an index to use.

     

    This is pretty disappointing. I guess this reinforces my opinion about always using surrogate integers for relationships and indexes. However several decades ago ABL was supposed to be "easy" and folks just wanted to build FK relationships using a bunch of crappy character fields. Our database is full of character-based relationships and indexes. I don't understand the logic of creating a software developer platform that is marketed as the "easy" option, and then developers are encouraged to make unwise design choices. (Sorry about the rant. Nowadays in the cloud, there are more and more platforms that are targeted to low-code developers and these sorts of platforms/developers often create more problems than they solve, and everyone has to live with the consequences for the following decades. lol).

     

     

    If it isn't clear, my hope was that the second query would have been a lot faster. It should have been able to complete in the 0.8 seconds, using only the data found in the index itself. (The extra 3 seconds of work to read the base table are totally unnecessary. I'll see if the behavior changes without the use of record locks, but I am not getting my hopes up.)

    • David Beavon (Software)

      Removing the record locks involves a hint - "WITH (NOLOCK)".

      It decreased the duration of both types of queries by about 20%, and the one using an index is still taking longer as I expected.

       

  • David Beavon (Software)

    I opened a tech support case, and got a reply.

     

    "

    Our development team has confirmed that it is not possible to fetch the data directly from the index. 

    "

     

    ... so apparently the information that I received from a Progress consultant ten years ago was not correct.

    Hope this helps others.

    Selected as Best