Re: Question about explain of index scan
От | Hannu Krosing |
---|---|
Тема | Re: Question about explain of index scan |
Дата | |
Msg-id | 1125674494.12008.11.camel@fuji.krosing.net обсуждение исходный текст |
Ответ на | Re: Question about explain of index scan (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: Question about explain of index scan
|
Список | pgsql-hackers |
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>
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Stephen FrostДата:
Сообщение: Re: GRANT/roles problem: grant is shown as from login role