Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1btv7L-0003HZ-91 for pgsql-performance@arkaria.postgresql.org; Tue, 11 Oct 2016 11:21:07 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1btv7J-0004dE-Pq for pgsql-performance@arkaria.postgresql.org; Tue, 11 Oct 2016 11:21:05 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1btv5e-0002jX-CI for pgsql-performance@postgresql.org; Tue, 11 Oct 2016 11:19:22 +0000 Received: from mail-lf0-x236.google.com ([2a00:1450:4010:c07::236]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1btv5b-0005Wy-65 for pgsql-performance@postgresql.org; Tue, 11 Oct 2016 11:19:20 +0000 Received: by mail-lf0-x236.google.com with SMTP id b75so35863524lfg.3 for ; Tue, 11 Oct 2016 04:19:18 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=subject:to:references:cc:from:message-id:date:user-agent :mime-version:in-reply-to:content-transfer-encoding; bh=ugPv5EgBPXbO/Sh04qC3yEsOLvmGD1sBJjxnqgPnC54=; b=aAzGc2uKT48UfEkp/7lb9+ogYZo3hGFORQZjJbfa1QKVd8qfXvxTI5mHZS2f+VGrS4 3K+aPRb0mqEtKjjiutG8NJQ3w44WzAstZT442E1z2m8cc+9K54FiMWL1O61wk8WaVZRN zZV4ZhApR+gQ8zYaNe6m08zcmCw9L4fD0KKAWhK6kCCcYDHQtBjjypz6LyZRhi8iq705 xe5XAUB0uX5F+DELDp34DkOt6ZJvdJSUl4gscXtpf+ix7DEiACh4rTKN0NxqvRMq4h/g peG7iOcC8YneWKHjwHhoG3INkdDVmTgzX9JMLY0W6TXS0Tbt6r2w1xRdJV1hiUD8qd7r KDkA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:subject:to:references:cc:from:message-id:date :user-agent:mime-version:in-reply-to:content-transfer-encoding; bh=ugPv5EgBPXbO/Sh04qC3yEsOLvmGD1sBJjxnqgPnC54=; b=V3Ok+l4S5UNG4i0YeeQuXqLZ9+N9dV+EYdAlIlxX0ZqYxQnbV/m5+E4p7x5WksHSMy sAOcIGp8uFAft6mF4tYCFGH4IPJO4DSaCGCunkUECY6C39Bz5wa8QNoWzMH0j6XNba9K HLYsp3qm3e2mV586jNvrErJkoUdaBqIfXAyV/RQBkQTD5IGwm12etljloJ/mY1R7x0Vn Ca+UD3Y3O+N05XzpAuomFFDGUi9FXFDDe8rx4oudlk8WVivF1VIptujFKhNV14c74AYi z2ieGqOFCoxO4pbs7F03An38lg3OJiG3lJKRMps7rNza695LIeAQ+h5jO7loDrSpRqD2 nIDQ== X-Gm-Message-State: AA6/9RkPEQbgyzGm86HhShKbSHYKNSbGWng8ayPQ3C6CKwKBjRW5dd9Bw0NMtCIhN0Augg== X-Received: by 10.25.150.6 with SMTP id y6mr2109256lfd.160.1476184756229; Tue, 11 Oct 2016 04:19:16 -0700 (PDT) Received: from [192.168.20.69] (bramka-zew.dfqs.pl. [194.126.222.22]) by smtp.googlemail.com with ESMTPSA id s63sm784335lja.49.2016.10.11.04.19.14 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Tue, 11 Oct 2016 04:19:15 -0700 (PDT) Subject: Re: Why query plan is different? To: Pavel Stehule References: <8aa28a9c-95c1-a8bb-e542-e7e4fd499c27@gmail.com> Cc: postgres performance list From: Andrzej Zawadzki Message-ID: <9c1b7ddf-6d26-78e3-a83d-80930b10e58a@gmail.com> Date: Tue, 11 Oct 2016 13:19:14 +0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101 Thunderbird/45.4.0 MIME-Version: 1.0 In-Reply-To: Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit X-Pg-Spam-Score: -0.3 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org
On 11.10.2016 03:47, Pavel Stehule wrote:


2016-10-10 23:17 GMT+02:00 Andrzej Zawadzki <zawadaa@gmail.com>:
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?

what is default locales?

LATIN2 - that's why I use opclass.

--
Andrzej