Sorting on index is slow

roeland · #1 (**permalink**) 04-18-07, 09:48

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

Tyveleyn · #2 (**permalink**) 04-18-07, 18:14

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

roeland · #3 (**permalink**) 04-19-07, 02:42

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%'

Tyveleyn · #4 (**permalink**) 04-19-07, 06:12

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?

roeland · #5 (**permalink**) 04-19-07, 06:53

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)

Tyveleyn · #6 (**permalink**) 04-20-07, 03:22

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

Internet Services

Web Development

Digital Marketing

Consumer Tech