Re: [PERFORM] Why query plan is different?

[Andrzej Zawadzki]

On 10.10.2016 17:31, Andrzej Zawadzki wrote: Hi, Today, I noticed strange situation: The same query run on different servers has very different plan: Q: SELECT b.* FROM kredytob b  WHERE pesel = '22222222222'  ORDER BY b.id DESC LIMIT 1 Slow plan: "Limit  (cost=0.43..28712.33 rows=1 width=4) (actual time=2574.041..2574.044 rows=1 loops=1)" "  Output: id" "  Buffers: shared hit=316132 read=110001" "  ->  Index Scan Backward using kredytob_pkey on public.kredytob b  (cost=0.43..3244444.80 rows=113 width=4) (actual time=2574.034..2574.034 rows=1 loops=1)" "        Output: id" "        Filter: (b.pesel = '22222222222'::bpchar)" "        Rows Removed by Filter: 433609" "        Buffers: shared hit=316132 read=110001" "Planning time: 0.414 ms" "Execution time: 2574.139 ms" Fast plan: "Limit  (cost=115240.66..115240.66 rows=1 width=4) (actual time=463.275..463.276 rows=1 loops=1)" "  Output: id" "  Buffers: shared hit=14661 read=4576" "  ->  Sort  (cost=115240.66..115240.94 rows=112 width=4) (actual time=463.271..463.271 rows=1 loops=1)" "        Output: id" "        Sort Key: b.id DESC" "        Sort Method: top-N heapsort  Memory: 25kB" "        Buffers: shared hit=14661 read=4576" "        ->  Index Scan using kredytob_pesel_typkred_opclass_idx on public.kredytob b  (cost=0.43..115240.10 rows=112 width=4) (actual time=311.347..463.183 rows=5 loops=1)" "              Output: id" "              Index Cond: (b.pesel = '22222222222'::bpchar)" "              Buffers: shared hit=14661 read=4576" "Planning time: 0.383 ms" "Execution time: 463.324 ms" Data is almost equal - "slow" has a few more rows in table. ("Fast" is a copy from 1 am today). Why runtime is slower? I made another INDEX, without opclass: CREATE INDEX kredytob_pesel_typkred_idx   ON public.kredytob   USING btree   (pesel COLLATE pg_catalog."default", typkred); after that: analyze kredytob; And now: "Limit  (cost=333.31..333.31 rows=1 width=4) (actual time=0.100..0.102 rows=1 loops=1)" "  Output: id" "  Buffers: shared hit=8" "  ->  Sort  (cost=333.31..333.59 rows=114 width=4) (actual time=0.095..0.095 rows=1 loops=1)" "        Output: id" "        Sort Key: b.id DESC" "        Sort Method: top-N heapsort  Memory: 25kB" "        Buffers: shared hit=8" "        ->  Index Scan using kredytob_pesel_typkred_idx on public.kredytob b  (cost=0.43..332.74 rows=114 width=4) (actual time=0.046..0.065 rows=5 loops=1)" "              Output: id" "              Index Cond: (b.pesel = '22222222222'::bpchar)" "              Buffers: shared hit=8" "Planning time: 0.438 ms" "Execution time: 0.154 ms" So, what is a reason that "SLOW" server doesn't like opclass index? -- Andrzej