SKIP SCAN
A user on IDS 12.10.FC4 asked what "INDEX PATH (SKIP SCAN)" in a query plan actually means. Art Kagel described it as traversing the B+tree, scanning leaves for matching keys and skipping back up for other lead-key values; Fernando Nunes explained it derives from the MULTI_INDEX/star-join feature and sorts the rowids before hitting data pages so data access is more sequential, which can beat a sequential scan on large result sets (with a pre-11.70 rowid-ordering subquery workaround shown). On the user's real problem — a slow query where the index was on a column with 99% identical values — Fernando advised updating statistics and, mainly, rewriting the nested IN subqueries as a single EXISTS form pushing the item_id filter down. Mark Scranton questioned the need for sorting since leaf rowids are already ordered; Fernando clarified they're only sorted within each key value, and pointed to his blog post for details. No confirmation of the rewrite's result is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
IDS 12.10 FC4. I have plan explanation of a sub query, Subquery: --------- Estimated Cost: 23317 Estimated # of Rows Returned: 42147 1) informix.activity_status: INDEX PATH (SKIP SCAN) (1) Index Name: informix.activity_status_idx4 Index Keys: act_item_descript (Serial, fragments: ALL) Lower Index Filter: informix.activity_status.act_item_descript = 'dataset_info' Can someone give more detail about what the SKIP SCAN exactly does ? Thanks Frank --94eb2c050a4430fe210538b4c1e3
Index Skip Scan traverses the B+tree to a matching leaf and reads across the leaves until it finds a key that no longer matches, if other lead key matches are needed it traverses back up the tree and descends to the new key and scans again (hence the skip as opposed to a leaf scan). Used when searching on the lead key column(s) in a compound key index. Also used for index self join queries when the leading key column is NOT included in the search but the query has a filter on the second column in the compound key. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on 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 Thu, Jul 28, 2016 at 12:37 PM, FRANK <yunyaoqu@gmail.com> wrote: > IDS 12.10 FC4. > > I have plan explanation of a sub query, > > Subquery: > > --------- > > Estimated Cost: 23317 > > Estimated # of Rows Returned: 42147 > > 1) informix.activity_status: INDEX PATH (SKIP SCAN) > > (1) Index Name: informix.activity_status_idx4 > > Index Keys: act_item_descript (Serial, fragments: ALL) > > Lower Index Filter: > informix.activity_status.act_item_descript = 'dataset_info' > > Can someone give more detail about what the SKIP SCAN exactly does ? > > Thanks > Frank > > --94eb2c050a4430fe210538b4c1e3 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11443c58bdfa090538b4e08d
Yes.... The name is a bit confusing because it came from another feature (star schema optimized joins). SKIP SCAN means it will "order" the rowIDs it gets from the INDEX prior to access the data pages. The advantae is that the accesses to the data pages will tend to be more "sequential". Believe it or not, the effect can be dramatic when large number of rows match the index condition. A few years ago (before that feature was introduced) I had a test case showing how a sequential scan on a 1M rows table was fatser than a query that retrived 20k rows from the table. After the feature was introduced (11.70.xC1) it became much fatser than the sequential scan. A "workaround" on 11.50 was to run something like: SELECT a.* FROM test_data a, (SELECT rowid r FROM test_data c WHERE col1 BETWEEN 1000 AND 1400 ORDER BY 1) b On Thu, Jul 28, 2016 at 5:37 PM, FRANK <yunyaoqu@gmail.com> wrote: > IDS 12.10 FC4. > > I have plan explanation of a sub query, > > Subquery: > > --------- > > Estimated Cost: 23317 > > Estimated # of Rows Returned: 42147 > > 1) informix.activity_status: INDEX PATH (SKIP SCAN) > > (1) Index Name: informix.activity_status_idx4 > > Index Keys: act_item_descript (Serial, fragments: ALL) > > Lower Index Filter: > informix.activity_status.act_item_descript = 'dataset_info' > > Can someone give more detail about what the SKIP SCAN exactly does ? > > Thanks > Frank > > --94eb2c050a4430fe210538b4c1e3 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --94eb2c0549548f652f0538b4ebb3
Thanks Fernando and Art for the info! So, when SKIP SCAN is used with an index , it normally means it would return large percent of the table data , right? ( I checked the index, seems not an efficient one, it would return more than 90% of the rows. I might suggest to drop this inefficient index next if possible). Another question, I am not sure what I need do with the following "workaround" , SELECT a.* FROM test_data a, (SELECT rowid r FROM test_data c WHERE col1 BETWEEN 1000 AND 1400 ORDER BY 1) b Thanks Frank On Thu, Jul 28, 2016 at 12:49 PM, Fernando Nunes <domusonline@gmail.com> wrote: > Yes.... The name is a bit confusing because it came from another feature > (star schema optimized joins). > SKIP SCAN means it will "order" the rowIDs it gets from the INDEX prior to > access the data pages. > The advantae is that the accesses to the data pages will tend to be more > "sequential". > > Believe it or not, the effect can be dramatic when large number of rows > match the index condition. > > A few years ago (before that feature was introduced) I had a test case > showing how a sequential scan on a 1M rows table was fatser than a query > that retrived 20k rows from the table. > After the feature was introduced (11.70.xC1) it became much fatser than the > sequential scan. > > A "workaround" on 11.50 was to run something like: > > SELECT > a.* > FROM test_data a, (SELECT rowid r FROM test_data c WHERE col1 BETWEEN 1000 > AND 1400 ORDER BY 1) b > > On Thu, Jul 28, 2016 at 5:37 PM, FRANK <yunyaoqu@gmail.com> wrote: > > > IDS 12.10 FC4. > > > > I have plan explanation of a sub query, > > > > Subquery: > > > > --------- > > > > Estimated Cost: 23317 > > > > Estimated # of Rows Returned: 42147 > > > > 1) informix.activity_status: INDEX PATH (SKIP SCAN) > > > > (1) Index Name: informix.activity_status_idx4 > > > > Index Keys: act_item_descript (Serial, fragments: ALL) > > > > Lower Index Filter: > > informix.activity_status.act_item_descript = 'dataset_info' > > > > Can someone give more detail about what the SKIP SCAN exactly does ? > > > > Thanks > > Frank > > > > --94eb2c050a4430fe210538b4c1e3 > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --94eb2c0549548f652f0538b4ebb3 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11352ae07abf1d0538ba7703
On Fri, Jul 29, 2016 at 12:26 AM, FRANK <yunyaoqu@gmail.com> wrote: > Thanks Fernando and Art for the info! > > So, when SKIP SCAN is used with an index , it normally means it would > return large percent of the table data , right? ( I checked the index, > seems not an efficient one, it would return more than 90% of the rows. > I might suggest to drop this inefficient index next if possible). > Roughly yes, although for 90% it's highly debatable if it should use the index versus a full scan. I'm used to see the engine not use INDEX SKIP SCAN when it should/could. This case is the opposite. I'd suggest you check if your statistics are up to date... > > Another question, I am not sure what I need do with the > following "workaround" , > SELECT a.* FROM test_data a, (SELECT rowid r FROM test_data c WHERE col1 > BETWEEN 1000 AND 1400 ORDER BY 1) b > Nothing... This could be used in previous versions, when an INDEX SKIP SCAN would be good but not implemented. It illustrates (roughly) what the engine does. > > Thanks > Frank > > On Thu, Jul 28, 2016 at 12:49 PM, Fernando Nunes <domusonline@gmail.com> > wrote: > > > Yes.... The name is a bit confusing because it came from another feature > > (star schema optimized joins). > > SKIP SCAN means it will "order" the rowIDs it gets from the INDEX prior > to > > access the data pages. > > The advantae is that the accesses to the data pages will tend to be more > > "sequential". > > > > Believe it or not, the effect can be dramatic when large number of rows > > match the index condition. > > > > A few years ago (before that feature was introduced) I had a test case > > showing how a sequential scan on a 1M rows table was fatser than a query > > that retrived 20k rows from the table. > > After the feature was introduced (11.70.xC1) it became much fatser than > the > > sequential scan. > > > > A "workaround" on 11.50 was to run something like: > > > > SELECT > > a.* > > FROM test_data a, (SELECT rowid r FROM test_data c WHERE col1 BETWEEN > 1000 > > AND 1400 ORDER BY 1) b > > > > On Thu, Jul 28, 2016 at 5:37 PM, FRANK <yunyaoqu@gmail.com> wrote: > > > > > IDS 12.10 FC4. > > > > > > I have plan explanation of a sub query, > > > > > > Subquery: > > > > > > --------- > > > > > > Estimated Cost: 23317 > > > > > > Estimated # of Rows Returned: 42147 > > > > > > 1) informix.activity_status: INDEX PATH (SKIP SCAN) > > > > > > (1) Index Name: informix.activity_status_idx4 > > > > > > Index Keys: act_item_descript (Serial, fragments: ALL) > > > > > > Lower Index Filter: > > > informix.activity_status.act_item_descript = 'dataset_info' > > > > > > Can someone give more detail about what the SKIP SCAN exactly does ? > > > > > > Thanks > > > Frank > > > > > > --94eb2c050a4430fe210538b4c1e3 > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > -- > > Fernando Nunes > > Portugal > > > > http://informix-technology.blogspot.com > > My email works... but I don't check it frequently... > > > > --94eb2c0549548f652f0538b4ebb3 > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a11352ae07abf1d0538ba7703 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a114aa7281bab530538c2de1e
Thanks Fernando!
I updated the table statistics high and rerun the SQL job( a known
bad inefficient SQL job). The following is the detailed info.
You can see , it used index with SKIP SCAN on column act_item_descript ,
informix.activity_status: INDEX PATH (SKIP SCAN)
(1) Index Name: informix.activity_status_idx4
Index Keys: act_item_descript (Serial, fragments: ALL)
Lower Index Filter:
informix.activity_status.act_item_descript = 'dataset_info'
Which has 99% the same value ! ( 42147 vs 42150)
Thanks
Frank
1) IDS Version:
12.10.FC4W1XU
2) Table schema:
{ TABLE "informix".activity_status row size = 304 number of columns = 21
index size = 107 }
create table "informix".activity_status
(
status_id serial not null ,
activity_id integer not null ,
proc_cmd char(30) not null ,
processing_path char(20) not null ,
act_item_descript char(20) not null ,
item_id integer not null ,
act_start_dt datetime year to second,
activity_stage char(20) not null ,
activity_result char(100),
queue_number integer not null ,
creation_dt datetime year to second,
stage_trans_dt datetime year to second,
cycle_check_dt datetime year to second,
inactivity_count smallint,
act_hold_end_dt datetime year to second,
path_counter integer,
parent_act_itm_dsc char(20),
parent_item_id integer,
parent_path_name char(20),
parent_path_cntr integer,
session_id integer,
primary key (status_id) constraint "informix".activity_status_pk
) in dbdata01 extent size 20000 next size 4000 lock mode row;
revoke all on "informix".activity_status from "public" as "informix";
create index "informix".activity_sta_idx1 on "informix".activity_status
(activity_stage,status_id) using btree in dbdata00;
create index "informix".activity_sta_idx2 on "informix".activity_status
(item_id) using btree in dbdata01;
create index "informix".activity_status_idx4 on "informix".activity_status
(act_item_descript) using btree in dbdata01;
create index "informix".activity_status_idx5 on "informix".activity_status
(proc_cmd) using btree in dbdata01;
3) Table data info:
select count(*) from activity_status
(count(*)) 42150
select act_item_descript,count(*)
from activity_status
group by act_item_descript
order by act_item_descript
act_item_descript (count(*))
dataset_info 42147
file_receipt_info 2
order_spec 1
4) SQL
SELECT count(*) FROM dataset_info WHERE item_id=114555365 and (item_id IN
(SELECT item_id FROM dataset_info WHERE path_status='NEWPATH' OR item_id
IN (SELECT item_id FROM activity_status WHERE
act_item_descript='dataset_info')))
5) Plan explanation
Estimated Cost: 30918
Estimated # of Rows Returned: 1
1) informix.dataset_info: INDEX PATH
(1) Index Name: informix. 270_608
Index Keys: item_id (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: informix.dataset_info.item_id = 114555365
Index Key Filters: (informix.dataset_info.item_id = ANY <subquery>
)
Subquery:
---------
Estimated Cost: 30917
Estimated # of Rows Returned: 26640
1) informix.dataset_info: INDEX PATH
(1) Index Name: informix.dataset_info_idx1
Index Keys: path_status item_id (Key-Only) (Serial,
fragments: ALL)
Lower Index Filter: informix.dataset_info.path_status =
'NEWPATH'
(2) Index Name: informix. 270_608
Index Keys: item_id (Serial, fragments: ALL)
Lower Index Filter: informix.dataset_info.item_id = ANY
<subquery>
Subquery:
---------
Estimated Cost: 23317
Estimated # of Rows Returned: 42147
1) informix.activity_status: INDEX PATH (SKIP SCAN)
(1) Index Name: informix.activity_status_idx4
Index Keys: act_item_descript (Serial, fragments: ALL)
Lower Index Filter:
informix.activity_status.act_item_descript = 'dataset_info'
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 dataset_info
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 1 1 1 00:01.16 30918
type rows_prod est_rows rows_cons time
-------------------------------------------------
group 1 1 1 00:01.16
Subquery statistics:
--------------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 dataset_info
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 42132 26640 42132 00:00.91 30917
Subquery statistics:
--------------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 activity_status
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 42147 42147 42147 00:00.10 23318
type rows_sort est_rows rows_cons time
-------------------------------------------------
sort 42131 0 42147 00:00.13
On Fri, Jul 29, 2016 at 5:29 AM, Fernando Nunes <domusonline@gmail.com>
wrote:
> On Fri, Jul 29, 2016 at 12:26 AM, FRANK <yunyaoqu@gmail.com> wrote:
>
> > Thanks Fernando and Art for the info!
> >
> > So, when SKIP SCAN is used with an index , it normally means it would
> > return large percent of the table data , right? ( I checked the index,
> > seems not an efficient one, it would return more than 90% of the rows.
> > I might suggest to drop this inefficient index next if possible).
> >
>
> Roughly yes, although for 90% it's highly debatable if it should use the
> index versus a full scan.
> I'm used to see the engine not use INDEX SKIP SCAN when it should/could.
> This case is the opposite.
> I'd suggest you check if your statistics are up to date...
>
> >
> > Another question, I am not sure what I need do with the
> > following "workaround" ,
> > SELECT a.* FROM test_data a, (SELECT rowid r FROM test_data c WHERE col1
> > BETWEEN 1000 AND 1400 ORDER BY 1) b
> >
>
> Nothing... This could be used in previous versions, when an INDEX SKIP SCAN
> would be good but not implemented.
> It illustrates (roughly) what the engine does.
>
> >
> > Thanks
> > Frank
> >
> > On Thu, Jul 28, 2016 at 12:49 PM, Fernando Nunes <domusonline@gmail.com>
> > wrote:
> >
> > > Yes.... The name is a bit confusing because it came from another
> feature
> > > (star schema optimized joins).
> > > SKIP SCAN means it will "order" the rowIDs it gets from the INDEX prior
> > to
> > > access the data pages.
> > > The advantae is that the accesses to the data pages will tend to be
> more
> > > "sequential".
> > >
> > > Believe
I find it a bit hard to understand why it considers an INDEX PATH for the
second table. A dbschema -hd would be nice, but it won't change my thoughts
I suppose.
I may be missing something, but in any case the most time the query takes
seems to be on the first sub-query.
The schema for the first table would help also.
But more important (you could open a PMR to investigate if this plan is
"correct") is the fact that the query is inefficient because of the way
it's written.
At the top most level of the query, you have a condition on item_id
(item_id = VALUE AND item_id IN ... )
The optimizer is not smart enough to push down that conditions into the
sub-queries....
Your query is:
SELECT
count(*)
FROM
dataset_info
WHERE
item_id=114555365 and
(item_id IN ( SELECT
item_id
FROM
dataset_info
WHERE
path_status='NEWPATH' OR
item_id IN (
SELECT
item_id
FROM
activity_status
WHERE
act_item_descript='dataset_info'
)
)
)
This should be equivalent and better:
SELECT
count(*)
FROM
dataset_info
WHERE
item_id=114555365 AND path_status = 'NEWPATH' AND EXISTS (SELECT 1 FROM
activity_status WHERE act_item_descript = 'dataset_info' AND item_id =
114555365 )
Please check.
Regards
On Mon, Aug 1, 2016 at 8:46 PM, FRANK <yunyaoqu@gmail.com> wrote:
> Thanks Fernando!
>
> I updated the table statistics high and rerun the SQL job( a known
> bad inefficient SQL job). The following is the detailed info.
>
> You can see , it used index with SKIP SCAN on column act_item_descript ,
>
> informix.activity_status: INDEX PATH (SKIP SCAN)
>
> (1) Index Name: informix.activity_status_idx4
>
> Index Keys: act_item_descript (Serial, fragments: ALL)
>
> Lower Index Filter:
> informix.activity_status.act_item_descript = 'dataset_info'
>
> Which has 99% the same value ! ( 42147 vs 42150)
>
> Thanks
> Frank
>
> 1) IDS Version:
> 12.10.FC4W1XU
>
> 2) Table schema:
> { TABLE "informix".activity_status row size = 304 number of columns = 21
> index size = 107 }
> create table "informix".activity_status
> (
>
> status_id serial not null ,
>
> activity_id integer not null ,
>
> proc_cmd char(30) not null ,
>
> processing_path char(20) not null ,
>
> act_item_descript char(20) not null ,
>
> item_id integer not null ,
>
> act_start_dt datetime year to second,
>
> activity_stage char(20) not null ,
>
> activity_result char(100),
>
> queue_number integer not null ,
>
> creation_dt datetime year to second,
>
> stage_trans_dt datetime year to second,
>
> cycle_check_dt datetime year to second,
>
> inactivity_count smallint,
>
> act_hold_end_dt datetime year to second,
>
> path_counter integer,
>
> parent_act_itm_dsc char(20),
>
> parent_item_id integer,
>
> parent_path_name char(20),
>
> parent_path_cntr integer,
>
> session_id integer,
>
> primary key (status_id) constraint "informix".activity_status_pk
> ) in dbdata01 extent size 20000 next size 4000 lock mode row;
> revoke all on "informix".activity_status from "public" as "informix";>
> create index "informix".activity_sta_idx1 on "informix".activity_status
>
> (activity_stage,status_id) using btree in dbdata00;
> create index "informix".activity_sta_idx2 on "informix".activity_status
>
> (item_id) using btree in dbdata01;
> create index "informix".activity_status_idx4 on "informix".activity_status
>
> (act_item_descript) using btree in dbdata01;
> create index "informix".activity_status_idx5 on "informix".activity_status
>
> (proc_cmd) using btree in dbdata01;
>
> 3) Table data info:
>
> select count(*) from activity_status>
> (count(*)) 42150
>
> select act_item_descript,count(*)
> from activity_status
> group by act_item_descript
> order by act_item_descript>
> act_item_descript (count(*))
> dataset_info 42147
> file_receipt_info 2
> order_spec 1
>
> 4) SQL
>
> SELECT count(*) FROM dataset_info WHERE item_id=114555365 and (item_id IN>
> (SELECT item_id FROM dataset_info WHERE path_status='NEWPATH' OR item_id
>
> IN (SELECT item_id FROM activity_status WHERE
> act_item_descript='dataset_info')))
>
> 5) Plan explanation
>
> Estimated Cost: 30918
> Estimated # of Rows Returned: 1
> 1) informix.dataset_info: INDEX PATH
>
> (1) Index Name: informix. 270_608
>
> Index Keys: item_id (Key-Only) (Serial, fragments: ALL)
>
> Lower Index Filter: informix.dataset_info.item_id = 114555365
>
> Index Key Filters: (informix.dataset_info.item_id = ANY <subquery>
> )
>
> Subquery:
>
> ---------
>
> Estimated Cost: 30917
>
> Estimated # of Rows Returned: 26640
>
> 1) informix.dataset_info: INDEX PATH
>
> (1) Index Name: informix.dataset_info_idx1
>
> Index Keys: path_status item_id (Key-Only) (Serial,
> fragments: ALL)
>
> Lower Index Filter: informix.dataset_info.path_status =
> 'NEWPATH'
>
> (2) Index Name: informix. 270_608
>
> Index Keys: item_id (Serial, fragments: ALL)
>
> Lower Index Filter: informix.dataset_info.item_id = ANY
> <subquery>
>
> Subquery:
>
> ---------
>
> Estimated Cost: 23317
>
> Estimated # of Rows Returned: 42147
>
> 1) informix.activity_status: INDEX PATH (SKIP SCAN)
>
> (1) Index Name: informix.activity_status_idx4
>
> Index Keys: act_item_descript (Serial, fragments: ALL)
>
> Lower Index Filter:
> informix.activity_status.act_item_descript = 'dataset_info'
>
> Query statistics:
> -----------------
> Table map :
> ----------------------------
> Internal name Table name
> ----------------------------
> t1 dataset_info
> type table rows_prod est_rows rows_scan time est_cost
> -------------------------------------------------------------------
> scan t1 1 1 1 00:01.16 30918
> type rows_prod est_rows rows_cons time
> -------------------------------------------------
> group 1 1 1 00:01.16
>
> Subquery statistics:
> --------------------
> Table map :
> ----------------------------
> Internal name Table name
> ----------------------------
> t1 dataset_info
> type table rows_prod est_rows rows_scan time est_cost
> -------------------------------------------------------------------
> scan t1 42132 26640 42132 00:00.91 30917
>
> Subquery statistics:
> --------------------
> Table map :
> ----------------------------
> Internal name Table name
> ----------------------------
> t1 activity_status
> type table rows_prod est_rows rows_scan time est_cost
> -------------------------------------------------------------------
> scan t1 42147 42147 42147 00:00.10 23318
> type rows_sort e
My mistake....:
SELECT
count(*)
FROM
dataset_info
WHERE
item_id=114555365 AND (path_status = 'NEWPATH' OR EXISTS (SELECT 1 FROM
activity_status WHERE act_item_descript = 'dataset_info' AND item_id =
114555365 ))
Now it should be right....
On Tue, Aug 2, 2016 at 11:08 AM, Fernando Nunes <domusonline@gmail.com>
wrote:
> I find it a bit hard to understand why it considers an INDEX PATH for the
> second table. A dbschema -hd would be nice, but it won't change my thoughts
> I suppose.
> I may be missing something, but in any case the most time the query takes
> seems to be on the first sub-query.
> The schema for the first table would help also.
>
> But more important (you could open a PMR to investigate if this plan is
> "correct") is the fact that the query is inefficient because of the way
> it's written.
> At the top most level of the query, you have a condition on item_id
> (item_id = VALUE AND item_id IN ... )
> The optimizer is not smart enough to push down that conditions into the
> sub-queries....
>
> Your query is:
>
> SELECT
>
> count(*)
> FROM
>
> dataset_info
> WHERE
>
> item_id=114555365 and
>
> (item_id IN ( SELECT
>
> item_id
>
> FROM
>
> dataset_info
>
> WHERE
>
> path_status='NEWPATH' OR
>
> item_id IN (
>
> SELECT
>
> item_id
>
> FROM
>
> activity_status
>
> WHERE
>
> act_item_descript='dataset_info'
>
> )
>
> )
>
> )
>
> This should be equivalent and better:
>
> SELECT
>
> count(*)
> FROM
>
> dataset_info
> WHERE
>
> item_id=114555365 AND path_status = 'NEWPATH' AND EXISTS (SELECT 1 FROM
> activity_status WHERE act_item_descript = 'dataset_info' AND item_id =
> 114555365 )
>
> Please check.
> Regards
>
> On Mon, Aug 1, 2016 at 8:46 PM, FRANK <yunyaoqu@gmail.com> wrote:
>
> > Thanks Fernando!
> >
> > I updated the table statistics high and rerun the SQL job( a known
> > bad inefficient SQL job). The following is the detailed info.
> >
> > You can see , it used index with SKIP SCAN on column act_item_descript ,
> >
> > informix.activity_status: INDEX PATH (SKIP SCAN)
> >
> > (1) Index Name: informix.activity_status_idx4
> >
> > Index Keys: act_item_descript (Serial, fragments: ALL)
> >
> > Lower Index Filter:
> > informix.activity_status.act_item_descript = 'dataset_info'
> >
> > Which has 99% the same value ! ( 42147 vs 42150)
> >
> > Thanks
> > Frank
> >
> > 1) IDS Version:
> > 12.10.FC4W1XU
> >
> > 2) Table schema:
> > { TABLE "informix".activity_status row size = 304 number of columns = 21
> > index size = 107 }
> > create table "informix".activity_status
> > (
> >
> > status_id serial not null ,
> >
> > activity_id integer not null ,
> >
> > proc_cmd char(30) not null ,
> >
> > processing_path char(20) not null ,
> >
> > act_item_descript char(20) not null ,
> >
> > item_id integer not null ,
> >
> > act_start_dt datetime year to second,
> >
> > activity_stage char(20) not null ,
> >
> > activity_result char(100),
> >
> > queue_number integer not null ,
> >
> > creation_dt datetime year to second,
> >
> > stage_trans_dt datetime year to second,
> >
> > cycle_check_dt datetime year to second,
> >
> > inactivity_count smallint,
> >
> > act_hold_end_dt datetime year to second,
> >
> > path_counter integer,
> >
> > parent_act_itm_dsc char(20),
> >
> > parent_item_id integer,
> >
> > parent_path_name char(20),
> >
> > parent_path_cntr integer,
> >
> > session_id integer,
> >
> > primary key (status_id) constraint "informix".activity_status_pk
> > ) in dbdata01 extent size 20000 next size 4000 lock mode row;
> > revoke all on "informix".activity_status from "public" as "informix";> >
> > create index "informix".activity_sta_idx1 on "informix".activity_status
> >
> > (activity_stage,status_id) using btree in dbdata00;
> > create index "informix".activity_sta_idx2 on "informix".activity_status
> >
> > (item_id) using btree in dbdata01;
> > create index "informix".activity_status_idx4 on
> "informix".activity_status
> >
> > (act_item_descript) using btree in dbdata01;
> > create index "informix".activity_status_idx5 on
> "informix".activity_status
> >
> > (proc_cmd) using btree in dbdata01;
> >
> > 3) Table data info:
> >
> > select count(*) from activity_status> >
> > (count(*)) 42150
> >
> > select act_item_descript,count(*)
> > from activity_status
> > group by act_item_descript
> > order by act_item_descript> >
> > act_item_descript (count(*))
> > dataset_info 42147
> > file_receipt_info 2
> > order_spec 1
> >
> > 4) SQL
> >
> > SELECT count(*) FROM dataset_info WHERE item_id=114555365 and (item_id IN> >
> > (SELECT item_id FROM dataset_info WHERE path_status='NEWPATH' OR item_id
> >
> > IN (SELECT item_id FROM activity_status WHERE
> > act_item_descript='dataset_info')))
> >
> > 5) Plan explanation
> >
> > Estimated Cost: 30918
> > Estimated # of Rows Returned: 1
> > 1) informix.dataset_info: INDEX PATH
> >
> > (1) Index Name: informix. 270_608
> >
> > Index Keys: item_id (Key-Only) (Serial, fragments: ALL)
> >
> > Lower Index Filter: informix.dataset_info.item_id = 114555365
> >
> > Index Key Filters: (informix.dataset_info.item_id = ANY <subquery>
> > )
> >
> > Subquery:
> >
> > ---------
> >
> > Estimated Cost: 30917
> >
> > Estimated # of Rows Returned: 26640
> >
> > 1) informix.dataset_info: INDEX PATH
> >
> > (1) Index Name: informix.dataset_info_idx1
> >
> > Index Keys: path_status item_id (Key-Only) (Serial,
> > fragments: ALL)
> >
> > Lower Index Filter: informix.dataset_info.path_status =
> > 'NEWPATH'
> >
> > (2) Index Name: informix. 270_608
> >
> > Index Keys: item_id (Serial, fragments: ALL)
> >
> > Lower Index Filter: informix.dataset_info.item_id = ANY
> > <subquery>
> >
> > Subquery:
> >
> > ---------
> >
> > Estimated Cost: 23317
> >
> > Estimated # of Rows Returned: 42147
> >
> > 1) informix.activity_status: INDEX PATH (SKIP SCAN)
> >
> > (1) Index Name: informix.activity_status_idx4
> >
> > Index Keys: act_item_descript (Serial, fragments: ALL)
> >
> > Lower Index Filter:
> > informix.activity_status.act_item_descript = 'dataset_info'
> >
> > Query statistics:
> > -----------------
> > Table map :
> > ----------------------------
> > Internal name Table name
> > ----------------------------
> > t1 dataset_info
> > type table rows_prod est_rows rows_scan time est_cost
> > -------------------------------------------------------------------
> > scan t1 1 1 1 00:01.16 30918
> > type rows_pr
Either something changed since I last taught internals (many years ago) or .... (actually pulled out my old internals book). We deep dive into indexes, including duplicate indexes, and on the leaf level, all rowids are sorted already. Not sure why SKIP SCAN would have to sort them unless we changed something dramatically in a b+tree. The twigs would also reflect lo to high since the reflect high value on a leaf for initial entries and greater than for the last entry on that page. That's a major reason a volatile, highly duplicate index can be a performance issue - the rowids must stay sorted and if deleting/adding duplicates, the leaves and branch/twigs must be maintained. High volatility can result in shuffle/split/merge(s) as the maintenance/mods are done. I've been on more than one site where the engine was waiting on mostly indexes pages that were very hot. Making that index unique (or "less duplicate") almost always resulted in performance improvement. Thoughts? Mark Scranton The Mark Scranton Group mark@markscranton.com
The ROWIDs are sorted within each key value. But if we do a query with a range of values we have partial "sorts", one for each value within the range. As I wrote before, the sort comes from the MULTI INDEX feature. Actually, the directive to get a SKIP SCAN is the "MULTI_INDEX".... The idea was to filter out duplicate rowids from different keys (to avoid duplicating the data access). If needed I think I can provide a test case... Regards. On Mon, Aug 22, 2016 at 11:33 PM, MARK SCRANTON <mark@markscranton.com> wrote: > Either something changed since I last taught internals (many years ago) or > ..... (actually pulled out my old internals book). We deep dive into > indexes, > including duplicate indexes, and on the leaf level, all rowids are sorted > already. Not sure why SKIP SCAN would have to sort them unless we changed > something dramatically in a b+tree. The twigs would also reflect lo to high > since the reflect high value on a leaf for initial entries and greater than > for the last entry on that page. That's a major reason a volatile, highly > duplicate index can be a performance issue - the rowids must stay sorted > and > if deleting/adding duplicates, the leaves and branch/twigs must be > maintained. > High volatility can result in shuffle/split/merge(s) as the > maintenance/mods > are done. I've been on more than one site where the engine was waiting on > mostly indexes pages that were very hot. Making that index unique (or "less > duplicate") almost always resulted in performance improvement. > > Thoughts? > Mark Scranton > The Mark Scranton Group > mark@markscranton.com > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a113f8aae73e2e3053ab9a03c
Some more details from a previous blog post: http://informix-technology.blogspot.pt/2014/04/index-skip-scan.html Regards. On Tue, Aug 23, 2016 at 10:17 AM, Fernando Nunes <domusonline@gmail.com> wrote: > The ROWIDs are sorted within each key value. But if we do a query with a > range of values we have partial "sorts", one for each value within the > range. > As I wrote before, the sort comes from the MULTI INDEX feature. Actually, > the directive to get a SKIP SCAN is the "MULTI_INDEX".... > > The idea was to filter out duplicate rowids from different keys (to avoid > duplicating the data access). > If needed I think I can provide a test case... > > Regards. > > On Mon, Aug 22, 2016 at 11:33 PM, MARK SCRANTON <mark@markscranton.com> > wrote: > > > Either something changed since I last taught internals (many years ago) > or > > ..... (actually pulled out my old internals book). We deep dive into > > indexes, > > including duplicate indexes, and on the leaf level, all rowids are sorted > > already. Not sure why SKIP SCAN would have to sort them unless we changed > > something dramatically in a b+tree. The twigs would also reflect lo to > high > > since the reflect high value on a leaf for initial entries and greater > than > > for the last entry on that page. That's a major reason a volatile, highly > > duplicate index can be a performance issue - the rowids must stay sorted > > and > > if deleting/adding duplicates, the leaves and branch/twigs must be > > maintained. > > High volatility can result in shuffle/split/merge(s) as the > > maintenance/mods > > are done. I've been on more than one site where the engine was waiting on > > mostly indexes pages that were very hot. Making that index unique (or > "less > > duplicate") almost always resulted in performance improvement. > > > > Thoughts? > > Mark Scranton > > The Mark Scranton Group > > mark@markscranton.com > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --001a113f8aae73e2e3053ab9a03c > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a1134f8584d39fa053aba370c