Why sequential scan???
Jeff Larsen asked why a correlated subquery using MIN(message) with a WHERE on an indexed foreign-key column (driver) chose a SEQUENTIAL SCAN. Replies argued over MIN preventing index use (disputed: MIN is applied after row selection), but the consensus was that it's a cost decision: stale/missing UPDATE STATISTICS on a very volatile message-queue table, plus a tiny table spanning only a few pages, makes a scan cheaper than index I/O. Suggestions included running UPDATE STATISTICS, marking the table resident (7.30+), and benchmarking both plans via SET EXPLAIN/syssesprof. No confirmed outcome was posted.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Why is the subquery in the query below doing
a sequential scan? The column "driver" has a
foreign key constraint and is thus indexed.
QUERY:
------
select message, driver, type, data
from raco_message rm where status = 0
and message = (select min(message) from raco_message
where driver = rm.driver)
Estimated Cost: 3
Estimated # of Rows Returned: 1
1) qdb.rm: INDEX PATH
Filters: (qdb.rm.status = 0 AND qdb.rm.message = <subquery> )
(1) Index Keys: driver
Subquery:
---------
Estimated Cost: 2
Estimated # of Rows Returned: 1
1) qdb.raco_message: SEQUENTIAL SCAN
Filters: qdb.raco_message.driver = qdb.rm.driver
well, you are using 'min' which has to look thru every row to find which
is the min.
min has no index for its calculated results. however - you can - if you
have
ids udo 9.x build a 'functional index' which will store the results of
the min for you
as an index.
Jeff Larsen wrote:
> Why is the subquery in the query below doing
> a sequential scan? The column "driver" has a
> foreign key constraint and is thus indexed.
>
> QUERY:
> ------
> select message, driver, type, data
> from raco_message rm where status = 0
> and message = (select min(message) from raco_message
> where driver = rm.driver)>
> Estimated Cost: 3
> Estimated # of Rows Returned: 1
>
> 1) qdb.rm: INDEX PATH
>
> Filters: (qdb.rm.status = 0 AND qdb.rm.message = <subquery> )
>
> (1) Index Keys: driver
>
> Subquery:
> ---------
> Estimated Cost: 2
> Estimated # of Rows Returned: 1
>
> 1) qdb.raco_message: SEQUENTIAL SCAN
>
> Filters: qdb.raco_message.driver = qdb.rm.driver
In article <38b56569.266428864@client.ce.news.psi.net>,
larsen@qec.com (Jeff Larsen) wrote:
> Why is the subquery in the query below doing
> a sequential scan? The column "driver" has a
> foreign key constraint and is thus indexed.
>
> QUERY:
> ------
> select message, driver, type, data
> from raco_message rm where status = 0
> and message = (select min(message) from raco_message
> where driver = rm.driver)>
> Estimated Cost: 3
> Estimated # of Rows Returned: 1
>
> 1) qdb.rm: INDEX PATH
>
> Filters: (qdb.rm.status = 0 AND qdb.rm.message = <subquery> )
>
> (1) Index Keys: driver
>
> Subquery:
> ---------
> Estimated Cost: 2
> Estimated # of Rows Returned: 1
>
> 1) qdb.raco_message: SEQUENTIAL SCAN
>
> Filters: qdb.raco_message.driver = qdb.rm.driver
>
>
have you updated statistics on this table/column recently?
--
# unrm /
ksh: unrm: not found
# man cpio
Sent via Deja.com http://www.deja.com/
Before you buy.
mars1972@my-deja.com wrote:
I think you might have something here. This is a highly volatile
table (a real-time message queue), so the statistics would be meaningless
anyway. Thankfully it stays small enough that the sequential scan
isn't too much of a performance hit. In fact, the table is so
volatile that it is probably always in resident memory. If this is
the case, then I shouldn't be concerned about the sequential scan
anyway, right?
>In article <38b56569.266428864@client.ce.news.psi.net>,
> larsen@qec.com (Jeff Larsen) wrote:
>> Why is the subquery in the query below doing
>> a sequential scan? The column "driver" has a
>> foreign key constraint and is thus indexed.
>>
>> QUERY:
>> ------
>> select message, driver, type, data
>> from raco_message rm where status = 0
>> and message = (select min(message) from raco_message
>> where driver = rm.driver)>>
>> Estimated Cost: 3
>> Estimated # of Rows Returned: 1
>>
>> 1) qdb.rm: INDEX PATH
>>
>> Filters: (qdb.rm.status = 0 AND qdb.rm.message = <subquery> )
>>
>> (1) Index Keys: driver
>>
>> Subquery:
>> ---------
>> Estimated Cost: 2
>> Estimated # of Rows Returned: 1
>>
>> 1) qdb.raco_message: SEQUENTIAL SCAN
>>
>> Filters: qdb.raco_message.driver = qdb.rm.driver
>>
>>
>
>have you updated statistics on this table/column recently?
>
>--
># unrm /
>ksh: unrm: not found
># man cpio
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Jeff Larsen wrote in message <38b5bbb8.288523384@client.ce.news.psi.net>... >mars1972@my-deja.com wrote: > >I think you might have something here. This is a highly volatile >table (a real-time message queue), so the statistics would be meaningless >anyway. Thankfully it stays small enough that the sequential scan >isn't too much of a performance hit. In fact, the table is so >volatile that it is probably always in resident memory. If this is >the case, then I shouldn't be concerned about the sequential scan >anyway, right? Jeff --- I missed which version you are using. Provided you are at 7.30+, why not make the table "resident" and be done with it. Of course, you need to be careful not to run into that index saturation bug which was fixed in 7.30uc7, right Art? David Weis, Informix DBA/Consultant dweis@louisville-i-market.com Voice: 502-637-7316
Jeff Larsen wrote:
> mars1972@my-deja.com wrote:
>
> I think you might have something here. This is a highly volatile
> table (a real-time message queue), so the statistics would be meaningless
> anyway. Thankfully it stays small enough that the sequential scan
> isn't too much of a performance hit. In fact, the table is so
> volatile that it is probably always in resident memory. If this is
> the case, then I shouldn't be concerned about the sequential scan
> anyway, right?
>
Wrong. Sequential scans of large tables (that fit entirely into memory) are
still more expensive than indexed searches on columns that aren't highly
duplicate. In your case, if the "driver" column has more than a few unique
values, the index search would be better.
The absolutely best thing to do is to put in your thumb... and taste the
pudding. Copy the table into a test database, removing the FK constraint.
Set explain on. Run the query using a sequential scan. Examine syssesproffor "costs". Initialize stats. Do this over and over again until you have a
good estimate of costs using a sequential scan. Then, apply an index on
"driver" (simulating the FK). Repeat the tests and compare.
Rudy
Actually, Edward, the min function has NOTHING to do with the selection of
rows (the rows are selected, then the minimum is determined). The index
should be used here, IF it is less expensive than a sequential scan.
Expense is based on the statistics. So, Jeff --
1) Have you run update statistics on the table since you put data in it?
2) Does the table span more than 3 data pages? I suspect the optimizer
would consider it cheaper to read 3 data pages with a single sequential
fetch that to do 2 random I/Os (root of index and then data page). At
least, that has been my experience with other cost based optimizers.
Given the low cost estimate of the sequential scan, I would suspect one of
these two scenarios.
HTH,
Doug
"Edward Rosenthal" <edrosenthal@home.com> wrote in message
news:38B528EF.6F8502F9@home.com...
> well, you are using 'min' which has to look thru every row to find which
> is the min.
> min has no index for its calculated results. however - you can - if you
> have
> ids udo 9.x build a 'functional index' which will store the results of
> the min for you
> as an index.
>
> Jeff Larsen wrote:
>
> > Why is the subquery in the query below doing
> > a sequential scan? The column "driver" has a
> > foreign key constraint and is thus indexed.
> >
> > QUERY:
> > ------
> > select message, driver, type, data
> > from raco_message rm where status = 0
> > and message = (select min(message) from raco_message
> > where driver = rm.driver)> >
> > Estimated Cost: 3
> > Estimated # of Rows Returned: 1
> >
> > 1) qdb.rm: INDEX PATH
> >
> > Filters: (qdb.rm.status = 0 AND qdb.rm.message = <subquery> )
> >
> > (1) Index Keys: driver
> >
> > Subquery:
> > ---------
> > Estimated Cost: 2
> > Estimated # of Rows Returned: 1
> >
> > 1) qdb.raco_message: SEQUENTIAL SCAN
> >
> > Filters: qdb.raco_message.driver = qdb.rm.driver
>