Re: [HACKERS] Query plan degradation 8.2 -- 8.3
Josh Berkus [EMAIL PROTECTED] writes: It does the right thing if t_s_symb is declared as text instead of varchar. When it's varchar, even setting enable_sort off won't make it pick the right plan, which suggests that it fails to recognize that the index can match the query's ORDER BY. I'm guessing I overlooked a binary-compatibility case when I rejiggered the handling of PathKeys in connection with the NULLS FIRST/LAST stuff. No time to look deeper right now. Yeah, that looks like the case. We'll move it to TEXT for the tests right now, but I'll make sure we don't forget this bug during beta. Thanks! I've applied a patch that fixes this case, but I'm not yet 100% convinced that there are no other cases where it'll prevent matching things that should match. Please test. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Query plan degradation 8.2 -- 8.3
Tom, I've applied a patch that fixes this case, but I'm not yet 100% convinced that there are no other cases where it'll prevent matching things that should match. Please test. Will do. We're having trouble building from CVS on the TPCE test rig, so it'll wait for tommorrow's snapshot. -- Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Query plan degradation 8.2 -- 8.3
Josh Berkus [EMAIL PROTECTED] writes: I now have a simple test case which shows significant performance degradation on 8.3devel for a specific query, apparenly due to an unnecessary call to Top-N sort. It does the right thing if t_s_symb is declared as text instead of varchar. When it's varchar, even setting enable_sort off won't make it pick the right plan, which suggests that it fails to recognize that the index can match the query's ORDER BY. I'm guessing I overlooked a binary-compatibility case when I rejiggered the handling of PathKeys in connection with the NULLS FIRST/LAST stuff. No time to look deeper right now. regards, tom lane ---(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: [HACKERS] Query plan degradation 8.2 -- 8.3
Josh Berkus [EMAIL PROTECTED] writes: On Wednesday 30 May 2007 15:51, Josh Berkus wrote: I now have a simple test case which shows significant performance degradation on 8.3devel for a specific query, apparenly due to an unnecessary call to Top-N sort. I've tried to forward the test case to the lists but the package is 3.5m, so I'm putting it on pgFoundry instead: How recently did you check out your 8.3 tree? When I run it I get a bitmap index scan which I think might mean you're suffering from the same problem Tom found and fixed a few days ago. The planner is finding the bitmap index scan with the sort is the best possible plan but then discarding that option later leaving it with a suboptimal choice. The exact manifestation is somewhat different from what other people saw. iirc they saw sequential scans when there was an index scan available. But I suspect it's the same thing going on. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Query plan degradation 8.2 -- 8.3
Greg, How recently did you check out your 8.3 tree? It's the snapshot from 5/28, which means it was pulled from CVS on 5/27. So, recent. When I run it I get a bitmap index scan which I think might mean you're suffering from the same problem Tom found and fixed a few days ago. The planner is finding the bitmap index scan with the sort is the best possible plan but then discarding that option later leaving it with a suboptimal choice. Apparently. I'll do another build and check. It does the right thing if t_s_symb is declared as text instead of varchar. When it's varchar, even setting enable_sort off won't make it pick the right plan, which suggests that it fails to recognize that the index can match the query's ORDER BY. I'm guessing I overlooked a binary-compatibility case when I rejiggered the handling of PathKeys in connection with the NULLS FIRST/LAST stuff. No time to look deeper right now. Yeah, that looks like the case. We'll move it to TEXT for the tests right now, but I'll make sure we don't forget this bug during beta. Thanks! -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
