Re: Mysql 321 - Mysql 322 - msql
От | The Hermit Hacker |
---|---|
Тема | Re: Mysql 321 - Mysql 322 - msql |
Дата | |
Msg-id | Pine.BSF.4.05.9811281714060.4737-100000@thelab.hub.org обсуждение исходный текст |
Список | pgsql-hackers |
On Sat, 28 Nov 1998, The Hermit Hacker wrote: > On Sat, 28 Nov 1998, John Fieber wrote: > > > In working with the two, I've also found a couple complicated > > join queries where I just couldn't get the optimizer in > > PostgreSQL (6.3.2 and 6.4) to do the right thing, resulting in > > several minutes of processing per query, while mySQL did the same > > query in the blink of an eye. > > You mention v6.4 above, so could you provide us with a way of > "reproducing" the bug? Attached is the database scheme from pg_dump (there are a bunch of extraneous tables in the context of this query). I make no claims at all about the quality of the database design or the query, but both Sybase and mySQL execute it very quickly while PostgreSQL refuses to use the index on the codes table. $sheet in the query is the "user input" to and is an integer. (For the curious, this is part of http://fallout.campusview.indiana.edu/mapfinder. I can supply data if needed.) -john SELECT sheet.sheet_id, sheet.name, sheet.number, sheet.note, cat.call, cat.series, cat.main_entry, sheet.scale, ca.name as mtype, cb.name as prod, cc.name as proj, cd.name as pm, ce.name as format, sheet.coords FROM sheet, cat, codes ca, codes cb, codes cc, codes cd, codes ce WHERE sheet.sheet_id = $sheet AND sheet.cat_id = cat.cat_id AND sheet.mtype = ca.code_id AND sheet.prod = cb.code_id AND sheet.proj = cc.code_id AND sheet.pm = cd.code_id AND sheet.format = ce.code_id --0-1134614595-912283854=:795 Content-Type: TEXT/PLAIN; charset=US-ASCII; name="mf.schema" Content-Transfer-Encoding: BASE64 Content-ID: <Pine.BSF.4.05.9811281510540.795@fallout.campusview.indiana.edu> Content-Description: mapfinder schema Content-Disposition: attachment; filename="mf.schema" Q1JFQVRFIFRBQkxFICJvcGVuZmllbGRzIiAoInNoZWV0X2lkIiAiaW50NCIs ICJjb2RlIiAiaW50MiIsICJ2YWx1ZSIgImludDQiKTsNCkNSRUFURSBUQUJM RSAiY29kZXMiICgiY29kZV9pZCIgImludDQiLCAibmFtZSIgInRleHQiKTsN CkNSRUFURSBUQUJMRSAiY2F0IiAoImNhdF9pZCIgImludDQiLCAiY2FsbCIg InRleHQiLCAibWFpbl9lbnRyeSIgInRleHQiLCAic2VyaWVzIiAidGV4dCIp Ow0KQ1JFQVRFIFRBQkxFICJzaGVldCIgKCJmaWxfbm8iICJpbnQ0IiwgInNo ZWV0X2lkIiAiaW50NCIsICJuYW1lIiAidGV4dCIsICJudW1iZXIiICJ0ZXh0 IiwgIm5vdGUiICJ0ZXh0IiwgImhvbGRpbmdzIiAiaW50MiIsICJjYXRfaWQi ICJpbnQ0IiwgIm10eXBlIiAiaW50MiIsICJwcm9kIiAiaW50MiIsICJwcm9q IiAiaW50MiIsICJwbSIgImludDIiLCAiZm9ybWF0IiAiaW50MiIsICJzY2Fs ZSIgImludDQiLCAiY29vcmRzIiAiYm94Iik7DQpDUkVBVEUgVEFCTEUgImdu aXNfc3RhdGUiICgiaWQiICJpbnQ0IiwgImFiYnJldiIgdmFyY2hhcig0KSwg Im5hbWUiICJ0ZXh0Iik7DQpDUkVBVEUgVEFCTEUgImduaXNfZnR5cGUiICgi aWQiICJpbnQ0IiwgImFiYnJldiIgY2hhcig4KSwgIm5hbWUiICJ0ZXh0Iik7 DQpDUkVBVEUgVEFCTEUgImduaXNfY291bnR5IiAoImlkIiAiaW50NCIsICJz dGF0ZSIgImludDQiLCAibmFtZSIgInRleHQiKTsNCkNSRUFURSBUQUJMRSAi Z25pcyIgKCJmbmFtZSIgInRleHQiLCAiZm5hbWVfbGMiICJ0ZXh0IiwgImZ0 eXBlIiAiaW50NCIsICJzdGF0ZSIgImludDQiLCAiY291bnR5IiAiaW50NCIs ICJlbGV2YXRpb24iICJpbnQ0IiwgInBvcHVsYXRpb24iICJpbnQ0IiwgImxv Y2F0aW9uIiAicG9pbnQiKTsNCkNSRUFURSBGVU5DVElPTiAiY29kZXRleHQi ICgiaW50MiIgKSBSRVRVUk5TICJ0ZXh0IiBBUyAnU0VMRUNUIGNvZGVzLm5h bWUgd2hlcmUgY29kZXMuY29kZV9pZCA9ICQxOycgTEFOR1VBR0UgJ1NRTCc7 DQpDUkVBVEUgIElOREVYICJpX29wZW5maWVsZHMiIG9uICJvcGVuZmllbGRz IiB1c2luZyBidHJlZSAoICJzaGVldF9pZCIgImludDRfb3BzIiApOw0KQ1JF QVRFICBJTkRFWCAiaV9jb2RlcyIgb24gImNvZGVzIiB1c2luZyBidHJlZSAo ICJjb2RlX2lkIiAiaW50NF9vcHMiICk7DQpDUkVBVEUgIElOREVYICJpX2Nh dCIgb24gImNhdCIgdXNpbmcgaGFzaCAoICJjYXRfaWQiICJpbnQ0X29wcyIg KTsNCkNSRUFURSAgSU5ERVggImlfc2hlZXQiIG9uICJzaGVldCIgdXNpbmcg YnRyZWUgKCAic2hlZXRfaWQiICJpbnQ0X29wcyIgKTsNCkNSRUFURSAgSU5E RVggImlfc2hlZXRuYW1lIiBvbiAic2hlZXQiIHVzaW5nIGJ0cmVlICggIm5h bWUiICJ0ZXh0X29wcyIgKTsNCkNSRUFURSAgSU5ERVggImlfc2hlZXRjb29y ZHMiIG9uICJzaGVldCIgdXNpbmcgcnRyZWUgKCAiY29vcmRzIiAiYm94X29w cyIgKTsNCkNSRUFURSAgSU5ERVggImduaXNfc3RhdGVfaSIgb24gImduaXNf c3RhdGUiIHVzaW5nIGJ0cmVlICggImlkIiAiaW50NF9vcHMiICk7DQpDUkVB VEUgIElOREVYICJnbmlzX2NvdW50eV9pIiBvbiAiZ25pc19jb3VudHkiIHVz aW5nIGJ0cmVlICggImlkIiAiaW50NF9vcHMiICk7DQpDUkVBVEUgIElOREVY ICJnbmlzX2kiIG9uICJnbmlzIiB1c2luZyBidHJlZSAoICJmbmFtZV9sYyIg InRleHRfb3BzIiApOw0K --0-1134614595-912283854=:795--
В списке pgsql-hackers по дате отправления: