Thread: Query plan degradation 8.2 --> 8.3
All, 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: If you run the example query (badsql.sql), you get the following for 8.2, which is a Good Plan: Limit (cost=0.00..24.04 rows=20 width=64) (actual time=0.091..0.129 rows=20 loops=1) -> Index Scan using abc_idx_t_s_symb_dts on abc (cost=0.00..908.55 rows=756 width=64) (actual time=0.089..0.123 rows=20 loops=1) Index Cond: (((t_s_symb)::text = 'SYMPRA'::text) AND(t_dts >= '2006-02-15 00:00:00'::timestamp without time zone) AND (t_dts <= '2007-06-28 00:00:00'::timestamp without time zone)) Filter: ((t_st_id = 'CMPT'::bpchar) AND (t_ca_id <= 43000050000::bigint))Total runtime: 0.222 ms However, running the same against 8.3-snapshot-5-28 gives you: Limit (cost=631.85..631.90 rows=20 width=55) (actual time=2.325..2.336 rows=20 loops=1) -> Sort (cost=631.85..633.61 rows=704 width=55) (actual time=2.323..2.326 rows=20 loops=1) Sort Key: t_dts Sort Method: top-N heapsort Memory: 27kB -> BitmapHeap Scan on abc (cost=25.23..613.12 rows=704 width=55) (actual time=0.537..1.477 rows=843 loops=1) Recheck Cond: (((t_s_symb)::text = 'SYMPRA'::text) AND (t_dts >= '2006-02-15 00:00:00'::timestamp without time zone) AND (t_dts <= '2007-06-28 00:00:00'::timestamp without time zone)) Filter: ((t_ca_id <= 43000050000::bigint) AND (t_st_id= 'CMPT'::bpchar)) -> Bitmap Index Scan on abc_idx_t_s_symb_dts (cost=0.00..25.06 rows=704 width=0) (actual time=0.506..0.506 rows=843 loops=1) Index Cond: (((t_s_symb)::text = 'SYMPRA'::text) AND (t_dts >= '2006-02-15 00:00:00'::timestamp without time zone) AND (t_dts <= '2007-06-28 00:00:00'::timestamp without time zone))Total runtime: 2.460 ms .... and if you disable bitmap: Limit (cost=812.35..812.40 rows=20 width=55) (actual time=2.363..2.372 rows=20 loops=1) -> Sort (cost=812.35..814.11 rows=704 width=55) (actual time=2.360..2.363 rows=20 loops=1) Sort Key: t_dts Sort Method: top-N heapsort Memory: 27kB -> IndexScan using abc_idx_t_s_symb_dts on abc (cost=0.00..793.62 rows=704 width=55) (actual time=0.080..1.567 rows=843 loops=1) Index Cond: (((t_s_symb)::text = 'SYMPRA'::text) AND (t_dts >= '2006-02-15 00:00:00'::timestamp without time zone) AND (t_dts <= '2007-06-28 00:00:00'::timestamp without time zone)) Filter: ((t_ca_id <= 43000050000::bigint) AND (t_st_id= 'CMPT'::bpchar))Total runtime: 2.475 ms The problem appears to be that top-N heapsort is being called even when it's not needed, such as immediately after an indexscan. Is my assessment correct? -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco
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: ...ooops, how about a link for that: http://pgfoundry.org/docman/view.php/1000041/767/pg83_sortbug.tar.Z -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco
Josh Berkus <josh@agliodbs.com> 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
"Josh Berkus" <josh@agliodbs.com> 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
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
Josh Berkus <josh@agliodbs.com> 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
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