From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Andrzej Zawadzki <zawadaa(at)wp(dot)pl> |
Cc: | postgres performance list <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Why query plan is different? |
Date: | 2016-10-10 17:09:15 |
Message-ID: | CAFj8pRCFZGWtu5NuJ7pcVmXECTiC5wHfYJ7VjhgObcEz+AAzxw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
2016-10-10 17:31 GMT+02:00 Andrzej Zawadzki <zawadaa(at)wp(dot)pl>:
> 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"
>
here is backward index scan with - lot of rows is thrown
Rows Removed by Filter: 433609"
probably index definition on these servers are different
regards
Pavel
> " 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?
>
> --
> Andrzej Zawadzki
>
From | Date | Subject | |
---|---|---|---|
Next Message | Andrzej Zawadzki | 2016-10-10 20:51:07 | Re: Why query plan is different? |
Previous Message | Andrzej Zawadzki | 2016-10-10 15:31:28 | Why query plan is different? |