FW: [PERFORM] Simple join optimized badly?
Hello. Simply jumping on the bandwagon, just my 2 cents: why not just like in some other (commercial) databases: a statement to say: use index I know this is against all though but if even the big ones can not resist the pressure of their users, why not? Henk Sanders -Oorspronkelijk bericht- Van: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Bucky Jordan Verzonden: woensdag 11 oktober 2006 16:27 Aan: Tom Lane; Brian Herlihy CC: Postgresql Performance Onderwerp: Re: [PERFORM] Simple join optimized badly? Brian Herlihy [EMAIL PROTECTED] writes: What would it take for hints to be added to postgres? A *whole lot* more thought and effort than has been expended on the subject to date. Personally I have no use for the idea of force the planner to do exactly X given a query of exactly Y. You don't have exactly Y today, tomorrow, and the day after (if you do, you don't need a hint mechanism at all, you need a mysql-style query cache). IMHO most of the planner mistakes we see that could be fixed via hinting are really statistical estimation errors, and so the right level to be fixing them at is hints about how to estimate the number of rows produced for given conditions. Mind you that's still a plenty hard problem, but you could at least hope that a hint of that form would be useful for more than one query. Do I understand correctly that you're suggesting it might not be a bad idea to allow users to provide statistics? Is this along the lines of I'm loading a big table and touching every row of data, so I may as well collect some stats along the way and I know my data contains these statistical properties, but the analyzer wasn't able to figure that out (or maybe can't figure it out efficiently enough)? While it seems like this would require more knowledge from the user (e.g. more about their data, how the planner works, and how it uses statistics) this would actually be helpful/required for those who really care about performance. I guess it's the difference between a tool advanced users can get long term benefit from, or a quick fix that will probably come back to bite you. I've been pleased with Postgres' thoughtful design; recently I've been doing some work with MySQL, and can't say I feel the same way. Also, I'm guessing this has already come up at some point, but what about allowing PG to do some stat collection during queries? If you're touching a lot of data (such as an import process) wouldn't it be more efficient (and perhaps more accurate) to collect stats then, rather than having to re-scan? It would be nice to be able to turn this on/off on a per query basis, seeing as it could have pretty negative impacts on OLTP performance... - Bucky ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: FW: [PERFORM] Simple join optimized badly?
H.J. Sanders wrote: why not just like in some other (commercial) databases: a statement to say: use index I know this is against all though but if even the big ones can not resist the pressure of their users, why not? Yeah - some could not (e.g. Oracle), but some did (e.g. DB2), and it seemed (to me anyway) significant DB2's optimizer worked much better than Oracle's last time I used both of them (Oracle 8/9 and DB2 7/8). cheers Mark ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: FW: [PERFORM] Simple join optimized badly?
On Thu, Oct 12, 2006 at 10:59:23PM +1300, Mark Kirkwood wrote: H.J. Sanders wrote: why not just like in some other (commercial) databases: a statement to say: use index I know this is against all though but if even the big ones can not resist the pressure of their users, why not? Yeah - some could not (e.g. Oracle), but some did (e.g. DB2), and it seemed (to me anyway) significant DB2's optimizer worked much better than Oracle's last time I used both of them (Oracle 8/9 and DB2 7/8). If someone's going to commit to putting effort into improving the planner then that's wonderful. But I can't recall any significant planner improvements since min/max (which I'd argue was more of a bug fix than an improvement). In fact, IIRC it took at least 2 major versions to get min/max fixed, and that was a case where it was very clear-cut what had to be done. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: FW: [PERFORM] Simple join optimized badly?
Jim C. Nasby [EMAIL PROTECTED] writes: If someone's going to commit to putting effort into improving the planner then that's wonderful. But I can't recall any significant planner improvements since min/max (which I'd argue was more of a bug fix than an improvement). Hmph. Apparently I've wasted most of the last five years. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: FW: [PERFORM] Simple join optimized badly?
On Thu, Oct 12, 2006 at 10:44:20AM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: If someone's going to commit to putting effort into improving the planner then that's wonderful. But I can't recall any significant planner improvements since min/max (which I'd argue was more of a bug fix than an improvement). Hmph. Apparently I've wasted most of the last five years. Ok, now that I've actually looked at the release notes, I take that back and apologize. But while there's a lot of improvements that have been made, there's still some seriously tough problems that have been talked about for a long time and there's still no light at the end of the tunnel, like how to handle multi-column statistics. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: FW: [PERFORM] Simple join optimized badly?
On Thu, 2006-10-12 at 09:44, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: If someone's going to commit to putting effort into improving the planner then that's wonderful. But I can't recall any significant planner improvements since min/max (which I'd argue was more of a bug fix than an improvement). Hmph. Apparently I've wasted most of the last five years. I appreciate the work, and trust me, I've noticed the changes in the query planner over time. Thanks for the hard work, and I'm sure there are plenty of other thankful people too. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: FW: [PERFORM] Simple join optimized badly?
Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: If someone's going to commit to putting effort into improving the planner then that's wonderful. But I can't recall any significant planner improvements since min/max (which I'd argue was more of a bug fix than an improvement). Hmph. Apparently I've wasted most of the last five years. In my opinion your on-going well thought out planner improvements are *exactly* the approach we need to keep doing... Cheers Mark ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: FW: [PERFORM] Simple join optimized badly?
Jim C. Nasby wrote: Ok, now that I've actually looked at the release notes, I take that back and apologize. But while there's a lot of improvements that have been made, there's still some seriously tough problems that have been talked about for a long time and there's still no light at the end of the tunnel, like how to handle multi-column statistics. Yeah - multi-column stats and cost/stats for functions look the the next feature additions we need to get going on Cheers Mark ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
