Question about seq scans on a table
Leonardo asked why a multi-table join was doing a sequential scan on the 4-million-row sol_item_guia table despite up-to-date statistics and an index hint. Jack Parker suggested checking statistics or index suitability. Dave Griffen spotted the real cause: the filter value 00368550 was written unquoted, so the CHAR column co_aviso had to be converted to numeric, disabling index use. Quoting the literal ('00368550') fixed it — the new plan used index paths and the estimated cost dropped from 181311 to 15.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello everybody.
I have this issue, when im executing one query i had as a result of
this query seq scan.
here is part of the dbschema
>dbschema -t sol_item_guia -d edithorp
DBSCHEMA Schema Utility INFORMIX-SQL Version 9.40.FC9W2X3
Copyright IBM Corporation 1996, 2004 All rights reserved
Software Serial Number AAA#B000000
{ TABLE "edithor".sol_item_guia row size = 68 number of columns = 13
index size =
80 }
create table "edithor".sol_item_guia
(
nu_solicitud char(8) not null ,
nu_item decimal(4,0) not null ,
co_guia char(3) not null ,
an_edicion char(4) not null ,
Here the indexes
primary key (nu_solicitud,nu_item,co_guia,an_edicion)
);
revoke all on "edithor".sol_item_guia from "public";
alter table "edithor".sol_item_guia add constraint (foreign key
(co_subtitulo) references "edithor".mae_subtitulo constraint
"edithor".fk_sol_item_gui1);
alter table "edithor".sol_item_guia add constraint (foreign key
(co_aviso) references "edithor".aviso );
alter table "edithor".sol_item_guia add constraint (foreign key
(nu_solicitud,nu_item) references "edithor".sol_item );
alter table "edithor".sol_item_guia add constraint (foreign key
(nu_solicitud,co_guia,an_edicion) references "edithor".sol_guia
);
alter table "edithor".sol_item_guia add constraint (foreign key
(co_boceto) references "edithor".boceto );
The rowcount for ths table is:
>dbaccess edithorp << EOM
> select count(*) from sol_item_guia> EOM
Database selected.
(count(*))
4075702
1 row(s) retrieved.
Database closed.
And my explain:
QUERY:
------
SELECT DISTINCT { + INDEX(sol_item_guia co_aviso)}
edithor.aviso.co_aviso,
edithor.aviso.ti_aviso,
edithor.aviso.co_nad,
edithor.nad.no_figuracion,
edithor.nad.no_calle,
edithor.nad.nu_casa,
edithor.nad.nu_piso,
edithor.nad.nu_dpto,
edithor.nad.ac_direcc,
edithor.sol_item.co_item,
nvl(edithor.sol_item.co_subtitulo,edithor.sol_item.co_rubro) co_publica,
nvl(edithor.mae_subtitulo.no_subtitulo,edithor.mae_rubro.no_rubro_publica)
no_publica,
edithor.sol_item_guia.co_guia, edithor.sol_item_guia.an_edicion,
edithor.mae_guia.no_largo_guia
FROM
edithor.aviso,
edithor.nad,
edithor.sol_item,
edithor.sol_item_guia,
outer edithor.mae_rubro,
outer edithor.mae_subtitulo,
outer edithor.mae_guia
WHERE
(edithor.nad.co_nad = edithor.aviso.co_nad ) and
(edithor.sol_item_guia.nu_solicitud = edithor.sol_item.nu_solicitud ) and
( edithor.sol_item_guia.nu_item = edithor.sol_item.nu_item ) and
( edithor.sol_item_guia.co_aviso = edithor.aviso.co_aviso ) and
( edithor.mae_rubro.co_rubro = edithor.sol_item.co_rubro ) and
( edithor.sol_item_guia.co_guia = edithor.mae_guia.co_guia ) and
( edithor.mae_subtitulo.co_subtitulo =
edithor.sol_item.co_subtitulo ) and
( ( edithor.aviso.co_aviso = 00368550 ) )
Estimated Cost: 181311
Estimated # of Rows Returned: 1
1) edithor.sol_item_guia: SEQUENTIAL SCAN
Filters: edithor.sol_item_guia.co_aviso = 368550
2) edithor.aviso: INDEX PATH
(1) Index Keys: co_aviso (Serial, fragments: ALL)
Lower Index Filter: edithor.sol_item_guia.co_aviso =
edithor.aviso.co_aviso
NESTED LOOP JOIN
3) edithor.sol_item: INDEX PATH
(1) Index Keys: nu_solicitud nu_item (Serial, fragments: ALL)
Lower Index Filter: (edithor.sol_item_guia.nu_solicitud =
edithor.sol_item.nu_solicitud AND edithor.sol_item_guia.nu_item =
edithor.sol_item.nu_item )
NESTED LOOP JOIN
4) edithor.nad: INDEX PATH
(1) Index Keys: co_nad (Serial, fragments: ALL)
Lower Index Filter: edithor.nad.co_nad = edithor.aviso.co_nad
NESTED LOOP JOIN
5) edithor.mae_subtitulo: INDEX PATH
(1) Index Keys: co_subtitulo (Serial, fragments: ALL)
Lower Index Filter: edithor.mae_subtitulo.co_subtitulo =
edithor.sol_item.co_subtitulo
NESTED LOOP JOIN
6) edithor.mae_rubro: INDEX PATH
(1) Index Keys: co_rubro (Serial, fragments: ALL)
Lower Index Filter: edithor.mae_rubro.co_rubro =
edithor.sol_item.co_rubro
NESTED LOOP JOIN
7) edithor.mae_guia: INDEX PATH
(1) Index Keys: co_guia (Serial, fragments: ALL)
Lower Index Filter: edithor.sol_item_guia.co_guia =
edithor.mae_guia.co_guia
NESTED LOOP JOIN
So, anybody can explain to me, why Informix is doing sequetial scans ?
Today i run update statistics high for table and for eac column
Thanks in advance,
Leonardo
If the optimizer thinks it is faster to read the entire table than it is to
use an index then one of a few things is true:
1 - The optimizer thinks the table is empty or small (are statistics
updated?)
2 - Your indexes are so large and unrelated to the query that they don't
help
That's where I would start looking.
j.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of
Leonardo Santagostini
Sent: Monday, April 12, 2010 2:28 PM
To: ids@iiug.org
Subject: Question about seq scans on a table [19644]
Hello everybody.
I have this issue, when im executing one query i had as a result of
this query seq scan.
here is part of the dbschema
>dbschema -t sol_item_guia -d edithorp
DBSCHEMA Schema Utility INFORMIX-SQL Version 9.40.FC9W2X3
Copyright IBM Corporation 1996, 2004 All rights reserved
Software Serial Number AAA#B000000
{ TABLE "edithor".sol_item_guia row size = 68 number of columns = 13
index size =
80 }
create table "edithor".sol_item_guia
(
nu_solicitud char(8) not null ,
nu_item decimal(4,0) not null ,
co_guia char(3) not null ,
an_edicion char(4) not null ,
Here the indexes
primary key (nu_solicitud,nu_item,co_guia,an_edicion)
);
revoke all on "edithor".sol_item_guia from "public";
alter table "edithor".sol_item_guia add constraint (foreign key
(co_subtitulo) references "edithor".mae_subtitulo constraint
"edithor".fk_sol_item_gui1);
alter table "edithor".sol_item_guia add constraint (foreign key
(co_aviso) references "edithor".aviso );
alter table "edithor".sol_item_guia add constraint (foreign key
(nu_solicitud,nu_item) references "edithor".sol_item );
alter table "edithor".sol_item_guia add constraint (foreign key
(nu_solicitud,co_guia,an_edicion) references "edithor".sol_guia
);
alter table "edithor".sol_item_guia add constraint (foreign key
(co_boceto) references "edithor".boceto );
The rowcount for ths table is:
>dbaccess edithorp << EOM
> select count(*) from sol_item_guia> EOM
Database selected.
(count(*))
4075702
1 row(s) retrieved.
Database closed.
And my explain:
QUERY:
------
SELECT DISTINCT { + INDEX(sol_item_guia co_aviso)}
edithor.aviso.co_aviso,
edithor.aviso.ti_aviso,
edithor.aviso.co_nad,
edithor.nad.no_figuracion,
edithor.nad.no_calle,
edithor.nad.nu_casa,
edithor.nad.nu_piso,
edithor.nad.nu_dpto,
edithor.nad.ac_direcc,
edithor.sol_item.co_item,
nvl(edithor.sol_item.co_subtitulo,edithor.sol_item.co_rubro) co_publica,
nvl(edithor.mae_subtitulo.no_subtitulo,edithor.mae_rubro.no_rubro_publica)
no_publica,
edithor.sol_item_guia.co_guia, edithor.sol_item_guia.an_edicion,
edithor.mae_guia.no_largo_guia
FROM
edithor.aviso,
edithor.nad,
edithor.sol_item,
edithor.sol_item_guia,
outer edithor.mae_rubro,
outer edithor.mae_subtitulo,
outer edithor.mae_guia
WHERE
(edithor.nad.co_nad = edithor.aviso.co_nad ) and
(edithor.sol_item_guia.nu_solicitud = edithor.sol_item.nu_solicitud ) and
( edithor.sol_item_guia.nu_item = edithor.sol_item.nu_item ) and
( edithor.sol_item_guia.co_aviso = edithor.aviso.co_aviso ) and
( edithor.mae_rubro.co_rubro = edithor.sol_item.co_rubro ) and
( edithor.sol_item_guia.co_guia = edithor.mae_guia.co_guia ) and
( edithor.mae_subtitulo.co_subtitulo =
edithor.sol_item.co_subtitulo ) and
( ( edithor.aviso.co_aviso = 00368550 ) )
Estimated Cost: 181311
Estimated # of Rows Returned: 1
1) edithor.sol_item_guia: SEQUENTIAL SCAN
Filters: edithor.sol_item_guia.co_aviso = 368550
2) edithor.aviso: INDEX PATH
(1) Index Keys: co_aviso (Serial, fragments: ALL)
Lower Index Filter: edithor.sol_item_guia.co_aviso =
edithor.aviso.co_aviso
NESTED LOOP JOIN
3) edithor.sol_item: INDEX PATH
(1) Index Keys: nu_solicitud nu_item (Serial, fragments: ALL)
Lower Index Filter: (edithor.sol_item_guia.nu_solicitud =
edithor.sol_item.nu_solicitud AND edithor.sol_item_guia.nu_item =
edithor.sol_item.nu_item )
NESTED LOOP JOIN
4) edithor.nad: INDEX PATH
(1) Index Keys: co_nad (Serial, fragments: ALL)
Lower Index Filter: edithor.nad.co_nad = edithor.aviso.co_nad
NESTED LOOP JOIN
5) edithor.mae_subtitulo: INDEX PATH
(1) Index Keys: co_subtitulo (Serial, fragments: ALL)
Lower Index Filter: edithor.mae_subtitulo.co_subtitulo =
edithor.sol_item.co_subtitulo
NESTED LOOP JOIN
6) edithor.mae_rubro: INDEX PATH
(1) Index Keys: co_rubro (Serial, fragments: ALL)
Lower Index Filter: edithor.mae_rubro.co_rubro =
edithor.sol_item.co_rubro
NESTED LOOP JOIN
7) edithor.mae_guia: INDEX PATH
(1) Index Keys: co_guia (Serial, fragments: ALL)
Lower Index Filter: edithor.sol_item_guia.co_guia =
edithor.mae_guia.co_guia
NESTED LOOP JOIN
So, anybody can explain to me, why Informix is doing sequetial scans ?
Today i run update statistics high for table and for eac column
Thanks in advance,
Leonardo
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
Statistics are up to date. In a few hours i will extract the index size for each one of them. Thanks Leonardo
Leonardo Santagostini WROTE: >Hello everybody. >I have this issue, when im executing one query i had as a result of >this query seq scan. > <SNIP> >So, anybody can explain to me, why Informix is doing sequetial scans ? Leonardo, The only limiting criteria I find in your SQL is (edithor.aviso.co_aviso = 00368550), everything else looks to be joining criteria. It does look like aviso.co_aviso is a primary key which could be utilized. However, you did not provide a definition for this column. I'm guessing that this column is a Char. Since your query value of 00368550 is not quoted, it is treated as numeric. If the column is char, all column values would therefore need to be converted to numeric for comparison. This would explain the seq scan. If aviso.co_aviso is a char, put quotes around '00368550' and you'll likely see the seq scan disappear. Hope this helps, Dave Griffen
Dave, there is a foreign key constraint on that single column so there is an index on that column in the table. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Apr 12, 2010 at 5:49 PM, DAVE GRIFFEN <dgriffen@finishline.com>wrote: > Leonardo Santagostini WROTE: > >Hello everybody. > > >I have this issue, when im executing one query i had as a result of > >this query seq scan. > > <SNIP> > >So, anybody can explain to me, why Informix is doing sequetial scans ? > > Leonardo, > The only limiting criteria I find in your SQL is (edithor.aviso.co_aviso = > 00368550), everything else looks to be joining criteria. It does look like > aviso.co_aviso is a primary key which could be utilized. However, you did > not > provide a definition for this column. I'm guessing that this column is a > Char. > Since your query value of 00368550 is not quoted, it is treated as numeric. > If > the column is char, all column values would therefore need to be converted > to > numeric for comparison. This would explain the seq scan. If aviso.co_aviso > is > a char, put quotes around '00368550' and you'll likely see the seq scan > disappear. > > Hope this helps, > Dave Griffen > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016e684c2fe4f6660048411944f
Dave, you give me the clue.
Look my explain
QUERY:
------
SELECT DISTINCT
edithor.aviso.co_aviso,
edithor.aviso.ti_aviso,
edithor.aviso.co_nad,
edithor.nad.no_figuracion,
edithor.nad.no_calle,
edithor.nad.nu_casa,
edithor.nad.nu_piso,
edithor.nad.nu_dpto,
edithor.nad.ac_direcc,
edithor.sol_item.co_item,
nvl(edithor.sol_item.co_subtitulo,edithor.sol_item.co_rubro) co_publica,
nvl(edithor.mae_subtitulo.no_subtitulo,edithor.mae_rubro.no_rubro_publica)
no_publica,
edithor.sol_item_guia.co_guia, edithor.sol_item_guia.an_edicion,
edithor.mae_guia.no_largo_guia
FROM
edithor.aviso,
edithor.nad,
edithor.sol_item,
edithor.sol_item_guia,
outer edithor.mae_rubro,
outer edithor.mae_subtitulo,
outer edithor.mae_guia
WHERE
( edithor.sol_item_guia.co_aviso = edithor.aviso.co_aviso ) and
(edithor.nad.co_nad = edithor.aviso.co_nad ) and
(edithor.sol_item_guia.nu_solicitud = edithor.sol_item.nu_solicitud ) and
( edithor.sol_item_guia.nu_item = edithor.sol_item.nu_item ) and
( edithor.mae_rubro.co_rubro = edithor.sol_item.co_rubro ) and
( edithor.sol_item_guia.co_guia = edithor.mae_guia.co_guia ) and
( edithor.mae_subtitulo.co_subtitulo = edithor.sol_item.co_subtitulo ) and
( edithor.aviso.co_aviso = '00368550' )
Estimated Cost: 15
Estimated # of Rows Returned: 1
1) edithor.aviso: INDEX PATH
(1) Index Keys: co_aviso (Serial, fragments: ALL)
Lower Index Filter: edithor.aviso.co_aviso = '00368550'
2) edithor.sol_item_guia: INDEX PATH
(1) Index Keys: co_aviso im_item (Serial, fragments: ALL)
Lower Index Filter: edithor.sol_item_guia.co_aviso = edithor.aviso.co_aviso
NESTED LOOP JOIN
Thank you so much !!!
Regards,
Leonardo