Re: Sequential scans on a one row table ?

Andrew >1. Is it just a recording error that a one row table can contribute to the >seqscans count ? Would I be correct in discounting any seqscans on a one row >table when I'm looking at things like sysptprof in SMI ? It is not a recording error, the optimizer indeed does a seqscan because it does not find it worth reading the index for a single row. However you would be correct in discounting it. >2. How many rows should a table be before sequential scans on that table >hurt performance ? eg if a 100 row table was being seqscanned, would you be >inclined to put an index on that table as a matter of course ? I believe the cutoff is approximately 200 rows before the optimizer finds itself cost-justified in using an index, so below that, it would not be much use putting an index because the optimizer would not use it. HTH Sujit "Reardon, Andrew J" <Andrew.Reardon@Australia.Boeing.com> on 06/09/99 07:33:25 PM To: "'informix-list@iiug.org'" <informix-list@iiug.org> cc: (bcc: Sujit Pal) Subject: Sequential scans on a one row table ? Hi all Just had a look at my "onstat -p" and it was showing a terribly large number of seqscans (~100K), so I went in and had a look at the offending table. Turns out the table with the most seqscans against it has only got *one row* in it. There were some other tables having seqscans done on them, ranging in size from about 10 to 10K rows. My setup is: IDS 7.24.UC6 on Solaris 2.6 on a Sun E450 (3 CPU) with 1GB RAM. The database is on cooked UFS disks. I run update stats high nightly (probably overkill but hey I've got some spare CPU cycles so why not:) My questions are: 1. Is it just a recording error that a one row table can contribute to the seqscans count ? Would I be correct in discounting any seqscans on a one row table when I'm looking at things like sysptprof in SMI ? 2. How many rows should a table be before sequential scans on that table hurt performance ? eg if a 100 row table was being seqscanned, would you be inclined to put an index on that table as a matter of course ? Thanks! Andrew Reardon Boeing Australia Tel: +61 7 3306 3346 Mobile: 0419 745 831

On Thu, 10 Jun 1999 09:15:36 -0700, Sujit.Pal@BankAmerica.com wrote: >>2. How many rows should a table be before sequential scans on that table >>hurt performance ? eg if a 100 row table was being seqscanned, would you >be >>inclined to put an index on that table as a matter of course ? >I believe the cutoff is approximately 200 rows before the optimizer finds >itself cost-justified in using an index, so below that, it would not be >much use putting an index because the optimizer would not use it. There is no "cutoff" based on the number of rows. It will be determined by the i/o cost of doing a sequential scan vs. the i/o cost of doing an index scan. Unless the index scan results in fewer overall pages being read (data pages + index pages), the seq scan will be done. For example, a table with very small rows could use only 1 data page to store 225 rows. In that case, a seq scan would still be done because it would be 1 page i/o vs 2 for an indexed access. You would have to get to at least 3 data pages before an index access could possibly result in fewer pages read than the seq scan (2 pgs for index vs 3 for seq). Given the max # rows/page is 255, this puts us at at 511 rows possible before the index is the better access. Complicating this is the "CPU" cost the optimizer adds in, which makes an index access slightly higher (cost of searching the index node to find the key you are after). While this is not as high as i/o cost, it can influence the optimizer to go with a seq scan at the marginal point (i.e. where the i/o for the index access is only slightly lower than that for the seq scan.) Dave

In article <37680f80.29000360@nntp.best.ix.netcom.com>, David Kosenko <davek@summitdata.com> writes >On Thu, 10 Jun 1999 09:15:36 -0700, Sujit.Pal@BankAmerica.com wrote: >>>2. How many rows should a table be before sequential scans on that table >>>hurt performance ? eg if a 100 row table was being seqscanned, would you >>be >>>inclined to put an index on that table as a matter of course ? Yes, a few extra i/os and pages in memory should make little difference. The big advantage is that as long as you regularly run update stats then if the table grows then performance does not drop since the index is already there. i.e. the system automatically scales as data is added. PS I have seen a configuration table on our product go from 10 rows to 1500 rows to 0.5 million rows as a customer 'abused' product features!! >>I believe the cutoff is approximately 200 rows before the optimizer finds >>itself cost-justified in using an index, so below that, it would not be >>much use putting an index because the optimizer would not use it. > >There is no "cutoff" based on the number of rows. It will be >determined by the i/o cost of doing a sequential scan vs. the i/o cost >of doing an index scan. Unless the index scan results in fewer >overall pages being read (data pages + index pages), the seq scan will >be done. For example, a table with very small rows could use only 1 >data page to store 225 rows. In that case, a seq scan would still be >done because it would be 1 page i/o vs 2 for an indexed access. You >would have to get to at least 3 data pages before an index access >could possibly result in fewer pages read than the seq scan (2 pgs for >index vs 3 for seq). Given the max # rows/page is 255, this puts us >at at 511 rows possible before the index is the better access. > >Complicating this is the "CPU" cost the optimizer adds in, which makes >an index access slightly higher (cost of searching the index node to >find the key you are after). While this is not as high as i/o cost, >it can influence the optimizer to go with a seq scan at the marginal >point (i.e. where the i/o for the index access is only slightly lower >than that for the seq scan.) > >Dave > > -- David Williams