Search by index vs. sequential scan

Ladies and gentlemen, I've been running into a very interesting problem, and I'd appreciate your advice. When is it better to have a sequential scan vs. using an index in a query? To be specific, if we change the OPTCOMPIND parameter in /usr/informix/etc/onconfig from 2 to 0 (thereby telling the optimizer to prefer indexes over sequential scans), what kinds of queries are likely to suffer performance penalties? First let me give you some background and explain the particular problem we've been encountering, which is making us want to change OPTCOMPIND. In our application using Informix, we have a table that represents units of work to be done. We also have a daemon process that wakes up every N minutes, consults the table to see if there is any pending work, does the work, updates the table, and then goes back to sleep for another N minutes. Our table structure looks something like this: CREATE TABLE worktable ( worktableid SERIAL NOT NULL, daemonid INTEGER, workfilename VARCHAR(255), workdonetimestamp DATETIME YEAR TO SECOND ); CREATE UNIQUE INDEX worktable_idx1 ON worktable (worktableid); There are actually several different daemons looking at the same table, hence the daemonid. A daemon will do the following: SELECT * FROM worktable WHERE daemonid = <my own daemon ID> AND workdonetimestamp IS NULL If that query returns 0 rows, the daemon goes back to sleep. Otherwise, it fetches all those rows into an array and starts doing its work. Each row represents one work unit; for each row, the daemon starts a transaction and runs the following: UPDATE worktable SET workdonetimestamp = NULL WHERE daemonid = <my own daemon ID> AND workdonetimestamp IS NULL This is, of course, a no-op, but it causes that row to be locked; this is to ensure that nobody else will touch that row of the database until we're done with the work unit and can update the workdonetimestamp field. Once the daemon is done with that work unit, it runs: UPDATE worktable SET workdonetimestamp = <current time> WHERE daemonid = <my own daemon ID> AND workdonetimestamp IS NULL AND worktableid = <the work unit ID that was just finished> The daemon then does a COMMIT WORK and loops back to fetch another work unit from its internal array. The problem we'd been encountering was happening when two daemons ran at the same time. Let's call them A and B and say that A happened to start a few tenths of a second before B. A goes and finds its work units, locks one of them, and starts processing. Then B goes and finds its work units; so far everyone's happy. But when B tries to lock its first work unit, it failed, producing the following error: SQL statement error number -244. Could not do a physical-order read to fetch next row. SYSTEM error number -107. ISAM error: record is locked. Analysis with SET EXPLAIN ON revealed that both A and B's UPDATE statements were using SEQUENTIAL SCAN to find the row they were to update. When B's update cursor came across the row that A had locked, it gave up. Aha, we said, we need an index for this update! So we created one: CREATE INDEX worktable_idx2 ON worktable (worktableid, daemonid); But this failed to solve the problem. To our amazement, we discovered that the UPDATE statements were still using SEQUENTIAL SCAN (and therefore failing) even though they could have used INDEX PATH to go straight to the correct rows, bypassing the locked rows. Why was this? Further testing revealed that it only happened when the worktable was small; once worktable became fairly large, the queries were using INDEX PATH and everything was fine. Then we discovered an old post on comp.databases.informix talking about the OPTCOMPIND parameter, which can tell the optimizer whether to prefer SEQUENTIAL SCAN or INDEX PATH. Changing that value from 2 to 0 caused INDEX PATH to be used for all our updates, even when the table was tiny (just two rows, one for each daemon). Problem finally solved. So, to return to my original question: we now have an optimizer that is preferring indexes over sequential scans even in small tables. This is necessary in our case, and we can't really change it back (that "physical-order read" problem had been bugging us for a LONG time...). But I'm wondering: are we likely to sacrifice performance here? What are some examples of queries where INDEX PATH is a massive performance loss compared to SEQUENTIAL SCAN? (Minor performance losses we can live with). Hopefully some experienced Informix admins will be able to point me in the right direction here... -- Robin Munn <rmunn@pobox.com> http://www.rmunn.com/ PGP key ID: 0x6AFB6838 50FF 2478 CFFB 081A 8338 54F7 845D ACFD 6AFB 6838

On Tue, 20 May 2003 20:29:17 GMT, Robin Munn <rmunn@pobox.com> wrote: .. snip.. >The problem we'd been encountering was happening when two daemons ran at >the same time. Let's call them A and B and say that A happened to start >a few tenths of a second before B. A goes and finds its work units, >locks one of them, and starts processing. Then B goes and finds its work >units; so far everyone's happy. But when B tries to lock its first work >unit, it failed, producing the following error: > >SQL statement error number -244. >Could not do a physical-order read to fetch next row. >SYSTEM error number -107. >ISAM error: record is locked.> >Analysis with SET EXPLAIN ON revealed that both A and B's UPDATE >statements were using SEQUENTIAL SCAN to find the row they were to >update. When B's update cursor came across the row that A had locked, it >gave up. Aha, we said, we need an index for this update! So we created >one: > >CREATE INDEX worktable_idx2 ON worktable (worktableid, daemonid);> >But this failed to solve the problem. To our amazement, we discovered >that the UPDATE statements were still using SEQUENTIAL SCAN (and >therefore failing) even though they could have used INDEX PATH to go >straight to the correct rows, bypassing the locked rows. Why was this? >Further testing revealed that it only happened when the worktable was >small; once worktable became fairly large, the queries were using INDEX >PATH and everything was fine. Then we discovered an old post on >comp.databases.informix talking about the OPTCOMPIND parameter, which >can tell the optimizer whether to prefer SEQUENTIAL SCAN or INDEX PATH. >Changing that value from 2 to 0 caused INDEX PATH to be used for all our >updates, even when the table was tiny (just two rows, one for each >daemon). Problem finally solved. > How many records are in 'worktable'? It makes sense from the optimizer point-of-view to only use an index if necessary. OPTCOMPIND set to 2 will hint the optimizer to use a cost-based solution. If the table is small, then why bother with an index read if a sequential scan is 'better'. BTW, did you 'update statistics' for the table after the index was built? >So, to return to my original question: we now have an optimizer that is >preferring indexes over sequential scans even in small tables. This is >necessary in our case, and we can't really change it back (that >"physical-order read" problem had been bugging us for a LONG time...). >But I'm wondering: are we likely to sacrifice performance here? What are >some examples of queries where INDEX PATH is a massive performance loss >compared to SEQUENTIAL SCAN? (Minor performance losses we can live >with). > If the table isn't too big you could "SET LOCK MODE TO WAIT <seconds>" in your code. If there are only two rows (as listed above), that would allow the second process to wait a bit for the first process to finish up. >Hopefully some experienced Informix admins will be able to point me in >the right direction here...