Re: Question about explain of index scan

On R, 2005-09-02 at 10:31 -0400, Tom Lane wrote:
> Hannu Krosing <hannu(at)skype(dot)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_cmdtype character(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(at)skype(dot)net>