planner not choosing fastest estimated plan
От | Jeff Janes |
---|---|
Тема | planner not choosing fastest estimated plan |
Дата | |
Msg-id | CAMkU=1wmAk4U5KGQP8LUpOmPSgEedxM9ch-rD5MTOUo8oYMstg@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: planner not choosing fastest estimated plan
(Pavel Stehule <pavel.stehule@gmail.com>)
Re: planner not choosing fastest estimated plan (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-hackers |
I have a weird case where the planner doesn't choose the plan that it itself believes to be the fastest plan. If I disable seqscan, it then chooses a faster plan (faster in estimate and faster in reality) than the one chosen when all options were open to it. I can't figure out how this can be anything other than a bug. The *collapse_limit parameters are not restraining things. I've created a dummy self-contained test case that is a simple self-join of a partitioned table, with a function-based index. If I analyze the tables after the function-based indexes are in place, then the problems goes away. And that is the production solution. But still, a bug is a bug, even if there is a work around. This is using the default configuration with LANG=C in 9.2, 9.3, and 9.4. It was introduced in commit 5b7b5518d0ea56c422a19787, "Revise parameterized-path mechanism to fix assorted issues" I've tried compiling under OPTIMIZER_DEBUG, but the output did not mean anything to me. It looks like the only RELOPTINFO corresponding to the join, "RELOPTINFO (1 2)" only lists the HashJoin when enable_seqscan=on, and only contains NestLoop when enable_seqscan=off.I don't know why it doesn't list both in both casesand then choose the faster. create table foo1 as select lpad(g::text,7,'0') as val1, g as num1 from generate_series(1,100000) g; create table foo2 as select lpad(g::text,7,'0') as val1, g as num1 from generate_series(100001,1000000) g; alter table foo2 inherit foo1; create index on foo1(num1 ); create index on foo2(num1 ); analyze foo1; analyze foo2; create language plperl; CREATE OR REPLACE FUNCTION perlupper(text)RETURNS textLANGUAGE plperlIMMUTABLE COST 1000 AS $function$ return uc($_[0]); $function$; create index on foo1 (perlupper(val1)); create index on foo2 (perlupper(val1)); jjanes=# explain select a.num1 from foo1 a, foo1 b where perlupper(a.val1)=perlupper(b.val1) and b.num1 = 987845; QUERY PLAN ----------------------------------------------------------------------------------------Hash Join (cost=32789.00..538040.65rows=10000 width=4) Hash Cond: (perlupper(b.val1) = perlupper(a.val1)) -> Append (cost=0.00..16.64rows=2 width=8) -> Index Scan using foo1_num1_idx on foo1 b (cost=0.00..8.28 rows=1 width=8) Index Cond: (num1 = 987845) -> Index Scan using foo2_num1_idx on foo2b (cost=0.00..8.37 rows=1 width=8) Index Cond: (num1 = 987845) -> Hash (cost=15406.00..15406.00 rows=1000000width=12) -> Append (cost=0.00..15406.00 rows=1000000 width=12) -> Seq Scan on foo1 a (cost=0.00..1541.00 rows=100000 width=12) -> Seq Scan on foo2 a (cost=0.00..13865.00 rows=900000 width=12) jjanes=# set enable_seqscan TO off; jjanes=# explain select a.num1 from foo1 a, foo1 b where perlupper(a.val1)=perlupper(b.val1) and b.num1 = 987845; QUERY PLAN -------------------------------------------------------------------------------------------------Nested Loop (cost=17.14..60438.19rows=10000 width=4) -> Append (cost=0.00..16.64 rows=2 width=8) -> Index Scan using foo1_num1_idxon foo1 b (cost=0.00..8.28 rows=1 width=8) Index Cond: (num1 = 987845) -> Index Scan using foo2_num1_idx on foo2b (cost=0.00..8.37 rows=1 width=8) Index Cond: (num1 = 987845) -> Append (cost=17.14..30160.77 rows=5000 width=12) -> Bitmap Heap Scan on foo1 a (cost=17.14..3022.65 rows=500 width=12) Recheck Cond: (perlupper(val1)= perlupper(b.val1)) -> Bitmap Index Scan on foo1_perlupper_idx (cost=0.00..17.01 rows=500 width=0) Index Cond: (perlupper(val1) = perlupper(b.val1)) -> BitmapHeap Scan on foo2 a (cost=92.22..27138.12 rows=4500 width=12) Recheck Cond: (perlupper(val1) = perlupper(b.val1)) -> Bitmap Index Scan onfoo2_perlupper_idx (cost=0.00..91.10 rows=4500 width=0) Index Cond: (perlupper(val1) = perlupper(b.val1)) Cheers, Jeff
В списке pgsql-hackers по дате отправления: