Re: Question about explain of index scan

On R, 2005-09-02 at 10:31 -0400, Tom Lane wrote:
> Hannu Krosing <hannu@skype.net> writes:
> > If I get a plan like this, what will actually be performed if EXPLAIN
> > shows this:
> 
> >  Sort  (cost=12.90..12.91 rows=1 width=207)
> >    Sort Key: log_actionseq
> >    ->  Index Scan using sl_log_1_idx2_hu, sl_log_1_idx2_hu,
> > sl_log_1_idx2_hu, sl_log_1_idx2_hu on sl_log_1  (cost=0.00..12.89 rows=1
> > width=207)
> >          Index Cond: (
> >    ((log_xid < '1349053093') AND (log_xid >= '1349052761')) 
> > OR ((log_xid < '1349053093') AND (log_xid >= '1349052761')) 
> > OR ((log_xid < '1349053093') AND (log_xid >= '1349052761')) 
> > OR ((log_xid < '1349053093') AND (log_xid >= '1349052761'))
> >                      )
> 
> > Will the same range be scanned 4 times ?
> 
> Yes.  However, I don't understand how you got that result; AFAIK the
> planner should have eliminated the duplicate subclauses.  For example,
> in 8.0 I get

This was on 7.4, sorry for forgetting to mention it. I also edited out
xid types and filter expression. maybe that filter expression is also
something that is shown in a weird way for mulltiple range scans ?

the query was similar to this:

-----------------------------------------------------------------------------

select     log_origin, log_xid, log_tableid,     log_actionseq,
log_cmdtype, log_cmddata  from "_bbb_cluster".sl_log_1 where log_origin = 1 
and (  
( log_tableid in (3,9008,9007,9005,9004,2002,2001) 
and (log_xid < '1312955843'     and "_bbb_cluster".xxid_lt_snapshot(log_xid,
'1312950014:1312955843:''1312955836'',''1312955783'',''1312955806'',''1312950014'',''1312952044'''))

and (log_xid >= '1312942023'     and "_bbb_cluster".xxid_ge_snapshot(log_xid,
'1312942023:1312947935:''1312947917'',''1312947924'',''1312942023'',''1312946242'''))
) 
or 
( log_tableid in

(1002,1003,1013,1041,1037,1028,1026,1023,1031,1012,1048,1050,1046,1021,1019,1024,1027,1029,1025,1035,1011,1009,1010,1016,1032,1018,1030,1138)


and (log_xid < '1312955843'     and "_bbb_cluster".xxid_lt_snapshot(log_xid,
'1312950014:1312955843:''1312955836'',''1312955783'',''1312955806'',''1312950014'',''1312952044''')) 

and (log_xid >= '1312942023'     and "_bbb_cluster".xxid_ge_snapshot(log_xid,
'1312942023:1312947935:''1312947917'',''1312947924'',''1312942023'',''1312946242''')) 
)
or 
( log_tableid in
(7001,7008,7007,7004,7039,7002,7030,7018,7038,7003,7005,7006,7009,7011,7012,7013,7016,7021,
7022,7025,7026,7027,7028,7029,7031,7033,7034,7035,7036,7037,1075,9009,9011,9012,9013,9014,
9015,9016,9017,1051,1052,1053,1054,1055,1056,1057,1058,1059,1060,1061,1062,1063,1064,1065,
1066,1067,1068,1070,1071,1072,1073,1074,1076,1077,1078) 
and (log_xid < '1312955843'     and "_bbb_cluster".xxid_lt_snapshot(log_xid,
'1312950014:1312955843:''1312955836'',''1312955783'',''1312955806'',''1312950014'',''1312952044''')) 
and (log_xid >= '1312942023'     and "_bbb_cluster".xxid_ge_snapshot(log_xid,
'1312942023:1312947935:''1312947917'',''1312947924'',''1312942023'',''1312946242''')) 
)
or 
( log_tableid in

(7051,7050,7052,7053,7054,7055,7056,7057,7058,7059,7060,7061,7062,7063,7064,7065,7066,7067,7068,7069,7070,7071,7072,7073,7074)

and (log_xid < '1312955843'     and "_bbb_cluster".xxid_lt_snapshot(log_xid,
'1312950014:1312955843:''1312955836'',''1312955783'',''1312955806'',''1312950014'',''1312952044''')) 
and (log_xid >= '1312942023'     and "_bbb_cluster".xxid_ge_snapshot(log_xid,
'1312942023:1312947935:''1312947917'',''1312947924'',''1312942023'',''1312946242''')) ) 
) 
order by log_actionseq;

-----------------------------------------------------------------------------------

the table used is :

CREATE TABLE sl_log_1 (   log_origin integer,   log_xid xxid,   log_tableid integer,   log_actionseq bigint,
log_cmdtypecharacter(1),   log_cmddata text
 
);

CREATE INDEX sl_log_1_idx1 ON sl_log_1 USING btree (log_origin, log_xid,
log_actionseq);

ALTER TABLE sl_log_1 CLUSTER ON sl_log_1_idx1;

CREATE INDEX sl_log_1_idx2_hu ON sl_log_1 USING btree (log_xid);

-----------------------------------------------------------------------------------

to get this plan you need to disable seqscan.

without second index you get an indexscan using sl_log_1_idx1 for
log_origin (always 1 in my case) and a really heavy filter.

-- 
Hannu Krosing <hannu@skype.net>