Thread: BUG #9552: IndexOnlyScan startup_cost>0, why not equal 0?it's a bug?
The following bug has been logged on the website: Bug reference: 9552 Logged by: digoal.zhou Email address: digoal@126.com PostgreSQL version: 9.3.3 Operating system: CentOS 6.4 x64 Description: I belive index only scan can return tuple direct, it's not need to scan heappage, why it's startup_cost equal to index scan? I'ts a bug? The TEST below: digoal=# create table t11(id int primary key, info text); CREATE TABLE digoal=# insert into t11 select generate_series(1,100000),'test'; INSERT 0 100000 digoal=# explain select * from t11 where id=1; QUERY PLAN -------------------------------------------------------------------- Index Scan using t11_pkey on t11 (cost=0.29..4.31 rows=1 width=9) Index Cond: (id = 1) (2 rows) digoal=# explain select id from t11 where id=1; QUERY PLAN ------------------------------------------------------------------------- Index Only Scan using t11_pkey on t11 (cost=0.29..4.31 rows=1 width=4) Index Cond: (id = 1) (2 rows)
Re: BUG #9552: IndexOnlyScan startup_cost>0, why not equal 0?it's a bug?
From
Kyotaro HORIGUCHI
Date:
Hello, > I belive index only scan can return tuple direct, it's not need to scan > heappage, why it's startup_cost equal to index scan? > I'ts a bug? No. It is a result of reasonable calculus. > Index Scan using t11_pkey on t11 (cost=0.29..4.31 rows=1 width=9) Index scan runs through several disk (but usually on cache) blocks during descending down to the leaf node in the index tree and does comparisons for certain amount of index entries(tuples) in each index node with regard to btree. The figure made from the expected number of the comparisons multiplied by cpu_operator_cost. Each access methods (index types) has their own function to do that. =# select amname, amcostestimate from pg_am where amname = 'btree'; amname | amcostestimate --------+---------------- btree | btcostestimate The function 'btcostestimate' in selfuncs.c calculates it for btree index. You can see the details there if you wish. regards, -- Kyotaro Horiguchi NTT Open Source Software Center
digoal@126.com writes: > I belive index only scan can return tuple direct, it's not need to scan > heappage, why it's startup_cost equal to index scan? > I'ts a bug? No. Your test case fails to vacuum t11, so although the planner selects an index-only scan, it's still predicting that all the tuples will require heap visits to confirm visibility. The startup cost would not change in any case, since that's about index descent costs not heap visit costs. regards, tom lane
Re: BUG #9552: IndexOnlyScan startup_cost>0, why not equal 0?it's a bug?
From
"digoal@126.com"
Date:
SEksDQogICBUaGFua3MsIGJ1dCBJbmRleE9ubHlTY2FuJ3Mgc3RhcnR1cF9jb3N0IG5vdCBjb250 YWluIFZNIHNjYW4gY29zdD8NCg0KZGlnb2FsPSMgY3JlYXRlIHRhYmxlIHQxMShpZCBpbnQgcHJp bWFyeSBrZXksIGluZm8gdGV4dCk7DQpDUkVBVEUgVEFCTEUNCmRpZ29hbD0jIGluc2VydCBpbnRv IHQxMSBzZWxlY3QgZ2VuZXJhdGVfc2VyaWVzKDEsMTAwMDAwKSwndGVzdCc7DQpJTlNFUlQgMCAx MDAwMDANCmRpZ29hbD0jIHNlbGVjdCByZWx0dXBsZXMgZnJvbSBwZ19jbGFzcyB3aGVyZSByZWxu YW1lPSd0MTFfcGtleSc7DQpyZWx0dXBsZXMgDQotLS0tLS0tLS0tLQ0KICAgIDEwMDAwMA0KKDEg cm93KQ0KZGlnb2FsPSMgc2hvdyBjcHVfb3BlcmF0b3JfY29zdDsNCmNwdV9vcGVyYXRvcl9jb3N0 IA0KLS0tLS0tLS0tLS0tLS0tLS0tLQ0KMC4wMDI1DQooMSByb3cpDQpkaWdvYWw9IyBzZWxlY3Qg KGxvZygxMDAwMDApL2xvZygyLjApKSowLjAwMjU7DQogICAgID9jb2x1bW4/ICAgICAgDQotLS0t LS0tLS0tLS0tLS0tLS0tDQowLjA0MTUyNDEwMTE4NjA5Mg0KKDEgcm93KQ0KZGlnb2FsPSMgY3Jl YXRlIGV4dGVuc2lvbiBwYWdlaW5zcGVjdDsNCkNSRUFURSBFWFRFTlNJT04NCmRpZ29hbD0jIFNF TEVDVCAqIEZST00gYnRfbWV0YXAoJ3QxMV9wa2V5Jyk7DQptYWdpYyAgfCB2ZXJzaW9uIHwgcm9v dCB8IGxldmVsIHwgZmFzdHJvb3QgfCBmYXN0bGV2ZWwgDQotLS0tLS0tLSstLS0tLS0tLS0rLS0t LS0tKy0tLS0tLS0rLS0tLS0tLS0tLSstLS0tLS0tLS0tLQ0KMzQwMzIyIHwgICAgICAgMiB8ICAg IDMgfCAgICAgMSB8ICAgICAgICAzIHwgICAgICAgICAxDQooMSByb3cpDQpkaWdvYWw9IyBzZWxl Y3QgKDErMSkqNTAqMC4wMDI1Ow0KP2NvbHVtbj8gDQotLS0tLS0tLS0tDQogICAwLjI1MDANCigx IHJvdykNCmRpZ29hbD0jIHNlbGVjdCAwLjA0MTUyNDEwMTE4NjA5MiswLjI1MDA7DQogICAgID9j b2x1bW4/ICAgICAgDQotLS0tLS0tLS0tLS0tLS0tLS0tDQowLjI5MTUyNDEwMTE4NjA5Mg0KKDEg cm93KQ0KDQojIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIw0KV2UgY2FuIHNlZSBpbmRleCBzY2Fu IGFuZCBpbmRleCBvbmx5IHNjYW4gc3RhcnR1cF9jb3N0IGlzIHNhbWUgYmVsb3csIGluZGV4b25s eXNjYW4gbm90IGNvbnRhaW4gdm0gc2NhbiBjb3N0Pw0KQW5kIGluZGV4IG9ubHkgc2NhbiByZWFs IHNsb3dlciB0aGFuIGluZGV4IHNjYW4uDQoNCmRpZ29hbD0jIGV4cGxhaW4gc2VsZWN0ICogZnJv bSB0MTEgd2hlcmUgaWQ9MTsNCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgUVVFUlkgUExB TiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgDQotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQ0KSW5kZXggU2NhbiB1 c2luZyB0MTFfcGtleSBvbiB0MTEgIChjb3N0PTAuMjkuLjQuMzEgcm93cz0xIHdpZHRoPTkpICAt LSANCiAgIEluZGV4IENvbmQ6IChpZCA9IDEpDQooMiByb3dzKQ0KZGlnb2FsPSMgZXhwbGFpbiBz ZWxlY3QgaWQgZnJvbSB0MTEgd2hlcmUgaWQ9MTsNCiAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICBRVUVSWSBQTEFOICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICANCi0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0NCkluZGV4IE9ubHkgU2NhbiB1c2luZyB0MTFfcGtleSBvbiB0MTEgIChjb3N0PTAu MjkuLjQuMzEgcm93cz0xIHdpZHRoPTQpICAtLSANCiAgIEluZGV4IENvbmQ6IChpZCA9IDEpDQoo MiByb3dzKQ0KDQpkaWdvYWw9IyBleHBsYWluIChhbmFseXplLHZlcmJvc2UsY29zdHMsYnVmZmVy cyx0aW1pbmcpIHNlbGVjdCBpZCBmcm9tIHQxMSB3aGVyZSBpZDwxMDAwMDsNCiAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIFFVRVJZIFBM QU4gICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICANCi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0NCkluZGV4IE9ubHkgU2NhbiB1c2luZyB0MTFfcGtleSBv biBwdWJsaWMudDExICAoY29zdD0wLjI5Li4yMDQuMjAgcm93cz05OTk1IHdpZHRoPTQpIChhY3R1 YWwgdGltZT0wLjA4OC4uMy42MjMgcm93cz05OTk5IGxvb3BzPTEpDQogICBPdXRwdXQ6IGlkDQog ICBJbmRleCBDb25kOiAodDExLmlkIDwgMTAwMDApDQogICBIZWFwIEZldGNoZXM6IDk5OTkgIA0K ICAgQnVmZmVyczogc2hhcmVkIGhpdD0yMg0KVG90YWwgcnVudGltZTogNC43NDggbXMNCig2IHJv d3MpDQoNCmRpZ29hbD0jIHZhY3V1bSBBTkFMWVpFIHQxMTsNClZBQ1VVTQ0KZGlnb2FsPSMgZXhw bGFpbiAoYW5hbHl6ZSx2ZXJib3NlLGNvc3RzLGJ1ZmZlcnMsdGltaW5nKSBzZWxlY3QgaWQgZnJv bSB0MTEgd2hlcmUgaWQ8MTAwMDA7DQogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICBRVUVSWSBQTEFOICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIA0KLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0NCkluZGV4IE9ubHkgU2NhbiB1c2luZyB0MTFfcGtleSBvbiBwdWJsaWMudDExICAoY29zdD0w LjI5Li4xOTEuNjQgcm93cz0xMDAyMCB3aWR0aD00KSAoYWN0dWFsIHRpbWU9MC4wODguLjIuNTk3 IHJvd3M9OTk5OSBsb29wcz0xKQ0KICAgT3V0cHV0OiBpZA0KICAgSW5kZXggQ29uZDogKHQxMS5p ZCA8IDEwMDAwKQ0KICAgSGVhcCBGZXRjaGVzOiAwICANCiAgIEJ1ZmZlcnM6IHNoYXJlZCBoaXQ9 OQ0KVG90YWwgcnVudGltZTogMy42MzQgbXMNCig2IHJvd3MpDQoNCg0KDQoNCg0KLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tDQq5q9Lm ysfSu7Gy19O1xMrCLCBJJ20gRGlnb2FsICwgSnVzdCBEbyBpdKOhDQq1wrjnKERpZ29hbC5aaG91 KQ0Kyv2+3b/ivLzK9b6twO0NCioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioq KioqKioqKioqDQq6vNbdy7m/rc34wue/xry809DP3rmry74NCrq81t3K0NfPvqO7qMK3MrrFwaq6 z7Tzz8NC1/kxMbLjDQrTyrHgOiAgIDMxMDAxMw0Kyta7+jogICArODYgMTM0ODQwMjE5NTMNCtf5 u/o6ICAgKzg2IDU3MSA4OTcxMDk0OA0KUVE6ICAgICAyNzY3MzI0MzENCmVtYWlsOiAgZGlnb2Fs Lnpob3VAbW9wby5jb20NCk1TTjogICAgenp6cXdhcmVAaG90bWFpbC5jb20NCkJsb2c6IGh0dHA6 Ly9ibG9nLjE2My5jb20vZGlnb2FsQDEyNi8NCkdpdGh1YjogaHR0cHM6Ly9naXRodWIuY29tL2Rp Z29hbA0KDQpGcm9tOiBUb20gTGFuZQ0KRGF0ZTogMjAxNC0wMy0xMyAyMTo1Ng0KVG86IGRpZ29h bA0KQ0M6IHBnc3FsLWJ1Z3MNClN1YmplY3Q6IFJlOiBbQlVHU10gQlVHICM5NTUyOiBJbmRleE9u bHlTY2FuIHN0YXJ0dXBfY29zdD4wLCB3aHkgbm90IGVxdWFsIDA/aXQncyBhIGJ1Zz8NCmRpZ29h bEAxMjYuY29tIHdyaXRlczoNCj4gSSBiZWxpdmUgaW5kZXggb25seSBzY2FuIGNhbiByZXR1cm4g dHVwbGUgZGlyZWN0LCBpdCdzIG5vdCBuZWVkIHRvIHNjYW4NCj4gaGVhcHBhZ2UsIHdoeSBpdCdz IHN0YXJ0dXBfY29zdCBlcXVhbCB0byBpbmRleCBzY2FuPw0KPiBJJ3RzIGEgYnVnPw0KDQpOby4g IFlvdXIgdGVzdCBjYXNlIGZhaWxzIHRvIHZhY3V1bSB0MTEsIHNvIGFsdGhvdWdoIHRoZSBwbGFu bmVyIHNlbGVjdHMNCmFuIGluZGV4LW9ubHkgc2NhbiwgaXQncyBzdGlsbCBwcmVkaWN0aW5nIHRo YXQgYWxsIHRoZSB0dXBsZXMgd2lsbCByZXF1aXJlDQpoZWFwIHZpc2l0cyB0byBjb25maXJtIHZp c2liaWxpdHkuDQoNClRoZSBzdGFydHVwIGNvc3Qgd291bGQgbm90IGNoYW5nZSBpbiBhbnkgY2Fz ZSwgc2luY2UgdGhhdCdzIGFib3V0IGluZGV4DQpkZXNjZW50IGNvc3RzIG5vdCBoZWFwIHZpc2l0 IGNvc3RzLg0KDQpyZWdhcmRzLCB0b20gbGFuZQ==