Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2007
    Location
    Belgium
    Posts
    3

    Unanswered: Sorting on index is slow

    I have this simple but very slow query:

    select * from tticst001100
    order by hash3

    There is an index on hash3:
    Index name Owner Type Cluster Columns
    ticst0011003abaan baan unique No hash3

    The index seems fine:
    >oncheck -cI baan:tticst001100

    Validating indexes for baan:baan.tticst001100...
    Index ticst0011001abaan
    Index fragment in DBspace datidx100
    Index ticst0011002abaan
    Index fragment in DBspace datidx100
    Index ticst0011003abaan
    Index fragment in DBspace datidx100

    Any idea why the index isn't used?

    >onstat -
    IBM Informix Dynamic Server Version 7.31.UD9 -- On-Line -- Up 26 days 06:09:42 -- 1597440 Kbytes

  2. #2
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    Hi,

    Set explain on and check the content of the 'sqexplain.out' file in the working directory after running the query. You'll see that a sequential scan is done and that's logical if there's no where clause to make a selection, where the index is needed for. In this case the order by will take (probably much) extra time because the natural order of rows is depended by time of insertion and that's not necessarily the order of the values in hash3. If you want to improve this change the index to clustered, but the trade off is extra time at inserting a new row.

    Regards,
    Hans
    Last edited by Tyveleyn; 04-18-07 at 19:35.

  3. #3
    Join Date
    Apr 2007
    Location
    Belgium
    Posts
    3
    Quote Originally Posted by Tyveleyn
    Set explain on and check the content of the 'sqexplain.out' file in the working directory after running the query. You'll see that a sequential scan is done and that's logical if there's no where clause to make a selection, where the index is needed for. In this case the order by will take (probably much) extra time because the natural order of rows is depended by time of insertion and that's not necessarily the order of the values in hash3. If you want to improve this change the index to clustered, but the trade off is extra time at inserting a new row.
    If I sort on an other index its fast:
    Code:
    QUERY:
    ------
    select * from tticst001100
    order by hash1
    
    Estimated Cost: 27326
    Estimated # of Rows Returned: 192913
    
    1) baan.tticst001100: INDEX PATH
    
        (1) Index Keys: hash1   (Serial, fragments: ALL)
    Code:
    QUERY:
    ------
    select * from tticst001100
    order by hash2
    
    Estimated Cost: 27655
    Estimated # of Rows Returned: 192913
    
    1) baan.tticst001100: INDEX PATH
    
        (1) Index Keys: hash2   (Serial, fragments: ALL)
    Code:
    QUERY:
    ------
    select * from tticst001100
    order by hash3
    
    Estimated Cost: 149642
    Estimated # of Rows Returned: 192913
    Temporary Files Required For: Order By
    
    1) baan.tticst001100: SEQUENTIAL SCAN
    Code:
    QUERY:
    ------
    select * from tticst001100
    where hash3 like "12%"
    order by hash3
    
    Estimated Cost: 3853
    Estimated # of Rows Returned: 4818
    
    1) baan.tticst001100: INDEX PATH
    
        (1) Index Keys: hash3   (Serial, fragments: ALL)
            Lower Index Filter: baan.tticst001100.hash3 LIKE '12%'
    Code:
    QUERY:
    ------
    select * from tticst001100
    where hash3 like "1%"
    order by hash3
    
    Estimated Cost: 146210
    Estimated # of Rows Returned: 187900
    Temporary Files Required For: Order By
    
    1) baan.tticst001100: SEQUENTIAL SCAN
    
        Filters: baan.tticst001100.hash3 LIKE '1%'
    Last edited by roeland; 04-19-07 at 04:00.

  4. #4
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    Obviously the hash3 index is to big for the query optimizer to decide for an indexed scan on the whole table. Do hash1, hash2 and hash3 have the same datatypes?

  5. #5
    Join Date
    Apr 2007
    Location
    Belgium
    Posts
    3
    Quote Originally Posted by Tyveleyn
    Obviously the hash3 index is to big for the query optimizer to decide for an indexed scan on the whole table. Do hash1, hash2 and hash3 have the same datatypes?
    hash1 CHAR(8) NOT NULL,
    hash2 CHAR(11) NOT NULL,
    hash3 CHAR(25) NOT NULL

    The strange thing is that on a differnent server with the same data, the query is fast.
    (We do a daily restore to a backup server)
    Last edited by roeland; 04-19-07 at 08:36.

  6. #6
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    Hi,

    Did you compare the onconfig settings of the two servers? Specifically the DS_TOTAL_MEMORY and DS_MAX_QUERIES params. Right now I don't know any other simple solution for the long index keys in 'ticst0011003abaan' than performing
    Code:
    ALTER INDEX ticst0011003abaan TO CLUSTER
    on a regular (nightly?) basis to sort the tablerows in the order of ticst0011003abaan. But then again, this only speeds up fetching the subsequent rows from the datapages, not the sorting of rows in the indexpages. So it may not make any significant difference at all.

    Anyway you can further check out the details in the Informix Performance Guide here: http://www-306.ibm.com/software/data...ry/ids_73.html
    You also can post this question in the SQL forum, there are some pretty smart and experienced DBA's active there on this matter.

    Good luck,
    Hans

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •